转载请注明出处：转载请注明出处：jiq•钦's
technical Blog
特别注意，这里讨论的是Oracle11g数据库，对于SQL Server，MYSQL等其他数据库不一定适用。
1、问题描述
当前系统中有两张表，一张是产品表，一张是附件表。
PRODUCT（PRODUCTID，PRODUCTNAME，PRODUCTPRICE，PICTURE）
ATTACHMENT（ATTACHMENTID，FILEPATH，BUSINESSTYPE，BUSINESSID）
其中产品表包含产品ID，产品名称，产品价格，图片等字段，附件表包含附件ID，附件全路径，附件所属模块编号（比如产品模块是105），附件所属记录ID（这里就是产品ID）。
需求是在查询全部产品界面显示所有产品的列表，并且每一项记录后面都需要一个“是否有视频”的字段指示当前产品记录是否包含视频。此外还要在上方的查询条件区域能够按照有无视频进行产品的查询。
2、解决思路
涉及左链接，正则表达式、函数、去重、ROWNUM等概念
方式1： 一条大而全的SQL语句
我做这个的时候，首先想到要用一个比较复杂的SQL语句拼出来一条SQL语句，能够查出最终要显示的所有产品记录。
所以首先想到左链接，因为需要保障左边产品表的所有记录都能够显示出来，不会有任何遗漏，所以写了这样一个SQL语句：
select T.*,A.BUSINESSTYPE from PRODUCT T left join ATTACHMENT A on T.PRODUCTID=A.BUSINESSID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)')
将产品表和附件表进行左链接，右边的附件表只有在满足BUSINESSID等于左边产品ID，并且附件是视频文件的情况下才连接到左表记录。
注意：其中where条件中用到了正则表达式判断，即regexp_like函数。
但是当一条产品有两个视频文件的时候问题就出来了，比如ID为331的产品有两个视频附件，那么ATTACHMENT表中就有两条附件记录满足：
T.PRODUCTID=A.BUSINESSID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)')
where条件，那么这两条附件记录都会连接到左表，因为左链接并不是说以左表为基准，左表有多少条，连接出来就是多少条，在这种情况下连接出来的记录数目就会比原有的左表的记录数目多。这就导致连接结果是这样：
PRODUCTID ...... BUSINESSID BUSINESSTYPE
331 331 105
331 331 105
你可能已经想到了，我们可以进行去重处理，于是出现下面语句：
select distinct T.*,A.BUSINESSTYPE from PRODUCT T left join ATTACHMENT A on T.PRODUCTID=A.BUSINESSID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)')
因为看似上面的重复的记录一模一样，所以使用distinct 关键字来进行记录去重处理。
很不幸这条语句编译出错，我开始也以为一个产品有两个以上视频的话，连接出来的重复的记录是一模一样的。
可惜在我这个场景下不一样，因为产品表里面有个PICTURE字段，是BLOB类型，存储图片的二进制数据，不允许用distinct关键字去重。
可以通过下面的方式进行去重：
select * from ( select distinct T.*,A.BUSINESSTYPE,ROW_NUMBER() OVER(PARTITION BY T.PRODUCTID order by T.PRODUCTNAME) rk from PRODUCT T left join ATTACHMENT A on T.PRODUCTID=A.BUSINESSID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)') ) where rk=1
即根据重复的PRODUCTID为基准，只要重复都只取第一条记录。
就这样一个满足条件的SQL就已经构造出来了，查询的结果就是产品表所有记录有且仅列出来一次，每条产品记录后面跟上一个字段BUSINESSTYPE，如果这个字段有值，标识这条产品记录有视频附件，如果没有值标识没有视频附件。
至于查询的时候，以有无视频作为查询条件，只需要在上面这条SQL外面再套一层，加上where BUSINESSTYPE='105'这个条件即可。
方式2： 借助函数等高级特性解决
将上面的方法与项目组中更加有经验的同事交流后发现自己这个方式不够优雅，更多地是觉得自己在数据库方面经验还太浅，完全可以有很多优雅的方式解决这个问题。
我上面的解决方式首先太过复杂，其次是通过连接之后记录中BUSINESSTYPE字段有没有值，是不是105来判断该产品有无视频。
更多时候我们可能不仅仅是需要保留连接后右边表的某个字段的值，而更多可能是需要针对某些列进行值的转换！！！
比如我们这里就想在产品表后面加一列，这列的值如果是0标识没有视频附件，如果是1标识有视频附件。另外的场景还有我们就想在产品表后面加上一列，这列的值表示产品拥有的附件的个数，这些信息哪儿来，肯定是从附件表来，但是光靠两个表的链接能做到么？
这个时候我们要借助函数来实现：
select distinct T.*,FUNC_HASATTACHMENT(t.PRODUCTID) from PRODUCT T
看这种方式多优雅，可拓展性多强，后面还可以加更多函数，你想要知道什么信息都可以，理论上函数都能实现。
下面看这个函数如何根据产品ID的值，返回是否有附件（0-没有有视频附件，1-有视频附件）
create OR REPLACE function FUNC_HASATTACHMENT(productID in decimal) return number is num number; result number; BEGIN result:=0; select count(ATTACHMENTID) into num from ATTACHMENT where BUSINESSTYPE='105' and BUSINESSID=productID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)'); if(num>0) then result:=1; else result:=0; endif; return result; END;
你看我们只需要传入产品ID，根据这个ID在附件表里面搞一搞就能搞出来这个产品有没有视频附件。
看来我们考虑一个数据库操作，并且感觉到稍微有点麻烦的时候，就要尝试跳出“大而全SQL”这个怪圈，尝试考虑用过程/函数，触发器，视图等高级特性来辅助解决！
下面附上函数的介绍：
CREATE [OR REPLACE] FUNCTION function_name
(arg1 [ { IN | OUT | IN OUT }] type1 [DEFAULT value1],
[arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]],
......
[argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]])
[ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type
IS | AS
<类型.变量的声明部分>
BEGIN
执行部分
RETURN expression
EXCEPTION
异常处理部分
END function_name;
更多细节参考：http://blog.csdn.net/jiyiqinlovexx/article/details/13417455
查询优化 --- 分批查询：
因为我们的产品数量还是比较多，几千条以上，而且还有很多图片，不可能一次查询返回所有图片，所以需要分批查询，每次点击都只加载一部分。
模式时这样的： 点击查询按钮，前台就会请求服务器上的RPC接口返回第一批500条数据，并且为本次查询创建一个唯一的sessionID，每次界面上点击“加载更多”按钮的时候，用这个sessionID，以及当前已经返回到了第几行这两个参数调用服务器上的RPC接口继续返回下一批数据。
如果点击加载全部按钮，就在客户端开启一个后台工作者线程一直跑这个“加载更多”这个过程。
核心就是记录当前这次查询回话的sessionID，当前已经返回了多少条记录了这些信息，以及分批查询用的SQL语句。
下面就是分批查询的SQL语句，返回第500-1000条记录：
select * from
(
select A.*,ROWNUM RN from
(
select distinct T.*,FUNC_HASATTACHMENT(t.PRODUCTID) from PRODUCT T
) A
where ROWNUM<={1000}
)
where RN>500
注：程序中500和1000是作为参数传递进去的。