一条大而全SQL与函数之间的抉择：谁是更为有效的SQL？，更为有效sql
转载请注明出处：转载请注明出处：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是作为参数传递进去的。
一下内容纯属抄袭~，希望有点用~
8.2 聚合函数的应用
聚合函数在数据库数据的查询分析中，应用十分广泛。本节将分别对各聚合函数的应用进行说明。
8.2.1 求和函数——SUM()
求和函数SUM( )用于对数据求和，返回选取结果集中所有值的总和。语法如下。
SELECT SUM(column_name)
FROM table_name
说明：SUM()函数只能作用于数值型数据，即列column_name中的数据必须是数值型的。
实例1 SUM函数的使用
从TEACHER表中查询所有男教师的工资总数。TEACHER表的结构和数据可参见5.2.1节的表5-1，下同。实例代码：
SELECT SUM(SAL) AS BOYSAL
FROM TEACHER
WHERE TSEX='男'
运行结果如图8.1所示。
图8.1 TEACHER表中所有男教师的工资总数
实例2 SUM函数对NULL值的处理
从TEACHER表中查询年龄大于40岁的教师的工资总数。实例代码：
SELECT SUM(SAL) AS OLDSAL
FROM TEACHER
WHERE AGE>=40
运行结果如图8.2所示。
图8.2 TEACHER表中所有年龄大于40岁的教师的工资总数
当对某列数据进行求和时，如果该列存在NULL值，则SUM函数会忽略该值。
8.2.2 计数函数——COUNT()
COUNT()函数用来计算表中记录的个数或者列中值的个数，计算内容由SELECT语句指定。使用COUNT函数时，必须指定一个列的名称或者使用星号，星号表示计算一个表中的所有记录。两种使用形式如下。
* COUNT(*)，计算表中行的总数，即使表中行的数据为NULL，也被计入在内。
* COUNT(column)，计算column列包含的行的数目，如果该列中某行数据为NULL，则该行不计入统计总数。
1．使用COUNT(*)函数对表中的行数计数
COUNT(*)函数将返回满足SELECT语句的WHERE子句中的搜索条件的函数。
实例3 COUNT(*)函数的使用
查询TEACHER表中的所有记录的行数。实例代码：
SELECT COUNT(*) AS TOTALITEM
FROM TEACHER
运行结果如图8.3所示。
图8.3 使用COUNT(*)函数对表中的行数计数
在该例中，SELECT语句中没有WHERE子句，那么认为表中的所有行都满足SELECT语句，所以SELECT语句将返回表中所有行的计数，结果与5.2.1节的表5-1列出的TEACHER表的数据相吻合。
如果DBMS在其系统表中存储了表的行数，COUNT(*)将很快地返回表的行数，因为这时，DBMS不必从头到尾读取表，并对物理表中的行计数，而直接从系统表中提取行的计数。而如果DBMS没有在系统表存储表的行数，将具有NOT NULL约束的列作为参数，使用COUNT( )函数，则可能更快地对表行计数。
注意
COUNT(*)函数将准确地返回表中的总行数，而仅当COUNT()函数的参数列没有NULL值时，才返回表中正确的行计数，所以仅当受NOT NULL限制的列作为参数时，才可使用COUNT( )函数代替COUNT(*)函数。
2．使用COUNT( )函数对一列中的数据计数
COUNT( )函数可用于对一列中的数据值计数。与忽略了所有列的......余下全文>>
select b.a,a.b,a.c from
(select B,sum(C) as c from TABLE group by B) a,
(select A,B from TABLE where (substr(A,-1) = 'a') b
where a.b=b.b
---------to LZ----------
你执行a和b两个查询大概用多长时间？
如果这两个子查询很快的话，建议在b字段建立索引
对了，还有一个问题，substr当用的时候，会使索引失效，你的(substr(A,-1) = 'a' 可以改成 A like '%a',反正都是一个意思
--------------------------
还有其他where条件的话，你可以考虑有索引的条件放在前边，如果是因为数据量太大的话造成查询速度慢，确实好办法不多，但是可以考虑用到分区，但是好像又不太值得
先用强制索引试验一下吧
表就是当前子查询的表，索引就是能用到的索引，譬如第一个子查询里用字段b上的索引，第二个子查询用字段a上的索引
select b.a,a.b,a.c from
(select /*+ index(表名,索引名) */ B,sum(C) as c from TABLE group by B) a,
(select /*+ index(表名,索引名) */ A,B from TABLE where A like = '%a') b
where a.b=b.b