select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOBField)) from TableName;
出处：http://zhouwf0726.itpub.net/post/9689/158090 
oracle分析函数 =========================================================== 作者: zhouwf0726(http: //zhouwf0726.itpub.net) 发表于: 2006.07. 25 12: 51 分类: oracle开发 出处：http: //zhouwf0726.itpub.net /post / 9689 / 158090 -- ------------------------------------------------------------- oracle分析函数 -- SQL*PLUS环境 -- 1、GROUP BY子句 -- CREATE TEST TABLE AND INSERT TEST DATA. create table students (id number( 15, 0), area varchar2( 10), stu_type varchar2( 2), score number( 20, 2)); insert into students values( 1, ' 111 ', ' g ', 80 ); insert into students values( 1, ' 111 ', ' j ', 80 ); insert into students values( 1, ' 222 ', ' g ', 89 ); insert into students values( 1, ' 222 ', ' g ', 68 ); insert into students values( 2, ' 111 ', ' g ', 80 ); insert into students values( 2, ' 111 ', ' j ', 70 ); insert into students values( 2, ' 222 ', ' g ', 60 ); insert into students values( 2, ' 222 ', ' j ', 65 ); insert into students values( 3, ' 111 ', ' g ', 75 ); insert into students values( 3, ' 111 ', ' j ', 58 ); insert into students values( 3, ' 222 ', ' g ', 58 ); insert into students values( 3, ' 222 ', ' j ', 90 ); insert into students values( 4, ' 111 ', ' g ', 89 ); insert into students values( 4, ' 111 ', ' j ', 90 ); insert into students values( 4, ' 222 ', ' g ', 90 ); insert into students values( 4, ' 222 ', ' j ', 89 ); commit; col score format 999999999999.99 -- A、GROUPING SETS select id,area,stu_type, sum(score) score from students group by grouping sets((id,area,stu_type),(id,area),id) order by id,area,stu_type; /* --------理解grouping sets select a, b, c, sum( d ) from t group by grouping sets ( a, b, c ) 等效于 select * from ( select a, null, null, sum( d ) from t group by a union all select null, b, null, sum( d ) from t group by b union all select null, null, c, sum( d ) from t group by c ) */ -- B、ROLLUP select id,area,stu_type, sum(score) score from students group by rollup(id,area,stu_type) order by id,area,stu_type; /* --------理解rollup select a, b, c, sum( d ) from t group by rollup(a, b, c); 等效于 select * from ( select a, b, c, sum( d ) from t group by a, b, c union all select a, b, null, sum( d ) from t group by a, b union all select a, null, null, sum( d ) from t group by a union all select null, null, null, sum( d ) from t ) */ -- C、CUBE select id,area,stu_type, sum(score) score from students group by cube(id,area,stu_type) order by id,area,stu_type; /* --------理解cube select a, b, c, sum( d ) from t group by cube( a, b, c) 等效于 select a, b, c, sum( d ) from t group by grouping sets( ( a, b, c ), ( a, b ), ( a ), ( b, c ), ( b ), ( a, c ), ( c ), () ) */ -- D、GROUPING /* 从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null, 如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列! */ select decode( grouping(id), 1, ' all id ',id) id, decode( grouping(area), 1, ' all area ',to_char(area)) area, decode( grouping(stu_type), 1, ' all_stu_type ',stu_type) stu_type, sum(score) score from students group by cube(id,area,stu_type) order by id,area,stu_type; -- 2、OVER()函数的使用 -- 1、RANK()、DENSE_RANK() 的、ROW_NUMBER()、CUME_DIST()、MAX()、AVG() break on id skip 1 select id,area,score from students order by id,area,score desc; select id,rank() over(partition by id order by score desc) rk,score from students; -- 允许并列名次、名次不间断 select id,dense_rank() over(partition by id order by score desc) rk,score from students; -- 即使SCORE相同，ROW_NUMBER()结果也是不同 select id,row_number() over(partition by ID order by SCORE desc) rn,score from students; select cume_dist() over( order by id) a, -- 该组最大row_number/所有记录row_number row_number() over ( order by id) rn,id,area,score from students; select id, max(score) over(partition by id order by score desc) as mx,score from students; select id,area, avg(score) over(partition by id order by area) as avg,score from students; -- 注意有无order by的区别 -- 按照ID求AVG select id, avg(score) over(partition by id order by score desc rows between unbounded preceding and unbounded following ) as ag,score from students; -- 2、SUM() select id,area,score from students order by id,area,score desc; select id,area,score, sum(score) over ( order by id,area) 连续求和, -- 按照OVER后边内容汇总求和 sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score) 100 * round(score / sum(score) over (), 4) "份额( %)" from students; select id,area,score, sum(score) over (partition by id order by area ) 连id续求和, -- 按照id内容汇总求和 sum(score) over (partition by id) id总和, -- 各id的分数总和 100 * round(score / sum(score) over (partition by id), 4) "id份额( %)", sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score) 100 * round(score / sum(score) over (), 4) "份额( %)" from students; -- 4、LAG(COL,n,default)、LEAD(OL,n,default) --取前后边N条数据 select id,lag(score, 1, 0) over( order by id) lg,score from students; select id,lead(score, 1, 0) over( order by id) lg,score from students; -- 5、FIRST_VALUE()、LAST_VALUE() select id,first_value(score) over( order by id) fv,score from students; select id,last_value(score) over( order by id) fv,score from students; /* 而对于last_value() over(order by id),结果是有问题的，因为我们没有按照id分区，所以应该出来的效果应该全部是90（最后一条）。 再看个例子就明白了： */ select id,last_value(score) over( order by rownum),score from students; /* ID LAST_VALUE(SCORE)OVER(ORDERBYR SCORE ---------------- ------------------------------ ---------------------- 1 80 80.00 1 80 80.00 1 89 89.00 1 68 68.00 2 80 80.00 2 70 70.00 2 60 60.00 2 65 65.00 3 75 75.00 3 58 58.00 3 58 58.00 3 90 90.00 4 89 89.00 4 90 90.00 4 90 90.00 4 89 89.00 16 rows selected 当使用last_value分析函数的时候，缺省的WINDOWING范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，在进行比较的时候从当前行向前进行比较，所以会出现上边的结果。加上如下的参数，结果就正常了。呵呵。默认窗口范围为所有处理结果。 */ select id,last_value(score) over( order by rownum RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),score from students; /* ID LAST_VALUE(SCORE)OVER(ORDERBYR SCORE ---------------- ------------------------------ ---------------------- 1 89 80.00 1 89 80.00 1 89 89.00 1 89 68.00 2 89 80.00 2 89 70.00 2 89 60.00 2 89 65.00 3 89 75.00 3 89 58.00 3 89 58.00 3 89 90.00 4 89 89.00 4 89 90.00 4 89 90.00 4 89 89.00 16 rows selected */ -- 给出一个例子再次理解分析函数 /* ********************************************************************************************http://www.itpub.net/620932.html 问题提出： 一个高级SQL语句问题 假设有一张表，A和B字段都是NUMBER， A B 1 2 2 3 3 4 4 有这样一些数据 现在想用一条SQL语句，查询出这样的数据 1－》2－》3—》4 就是说，A和B的数据表示一种连接的关系，现在想通过A的一个值，去查询A所对应的B值，直到B为NULL为止， 不知道这个SQL语句怎么写？请教高手！谢谢 ******************************************************************************************** */ -- 以下是利用分析函数的一个简单解答： -- start with connect by可以参考http://www.itpub.net/620427.html CREATE TABLE TEST(COL1 NUMBER( 18, 0),COL2 NUMBER( 18, 0)); INSERT INTO TEST VALUES( 1, 2); INSERT INTO TEST VALUES( 2, 3); INSERT INTO TEST VALUES( 3, 4); INSERT INTO TEST VALUES( 4, NULL); INSERT INTO TEST VALUES( 5, 6); INSERT INTO TEST VALUES( 6, 7); INSERT INTO TEST VALUES( 7, 8); INSERT INTO TEST VALUES( 8, NULL); INSERT INTO TEST VALUES( 9, 10); INSERT INTO TEST VALUES( 10, NULL); INSERT INTO TEST VALUES( 11, 12); INSERT INTO TEST VALUES( 12, 13); INSERT INTO TEST VALUES( 13, 14); INSERT INTO TEST VALUES( 14, NULL); select max(col) from( select SUBSTR(col, 1, CASE WHEN INSTR(col, ' -> ') > 0 THEN INSTR(col, ' -> ') - 1 ELSE LENGTH(col) END) FLAG,col from( select ltrim(sys_connect_by_path(col1, ' -> '), ' -> ') col from ( select col1,col2, CASE WHEN LAG(COL2, 1, NULL) OVER( ORDER BY ROWNUM) IS NULL THEN 1 ELSE 0 END FLAG from test ) start with flag = 1 connect by col1 =prior col2 ) ) group by flag ; -- 再次给出一个例子： -- 查找重复记录的方法，除了用count(*)，还可以用row_number()等函数实现 create table test(xm varchar2( 20),sfzhm varchar2( 20)); insert into test values( ' 1 ', ' 11111 '); insert into test values( ' 1 ', ' 11111 '); insert into test values( ' 2 ', ' 22222 '); insert into test values( ' 2 ', ' 22222 '); insert into test values( ' 2 ', ' 22222 '); insert into test values( ' 3 ', ' 33333 '); insert into test values( ' 3 ', ' 33333 '); insert into test values( ' 3 ', ' 33333 '); commit; select * from test a,( select xm,sfzhm from test group by xm,sfzhm having count( *) > 2 ) b where a.xm =b.xm and a.sfzhm =b.sfzhm select * from ( select xm,sfzhm, count( *) over(partition by xm,sfzhm) sl from test) where sl > 2; 看到很多人对于keep不理解，这里解释一下！ Returns the row ranked first using DENSE_RANK 2种取值： DENSE_RANK FIRST DENSE_RANK LAST 在keep (DENSE_RANK first ORDER BY sl) 结果集中再取max、min的例子。 SQL > select * from test; ID MC SL -- ------------------ -------------------- ------------------- 1 111 1 1 222 1 1 333 2 1 555 3 1 666 3 2 111 1 2 222 1 2 333 2 2 555 2 9 rows selected SQL > SQL > select id,mc,sl, 2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id), 3 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id) 4 from test 5 ; ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKLASTORDE -- ------------------ -------------------- ------------------- ------------------------------ ------------------------------ 1 111 1 111 666 1 222 1 111 666 1 333 2 111 666 1 555 3 111 666 1 666 3 111 666 2 111 1 111 555 2 222 1 111 555 2 333 2 111 555 2 555 2 111 555 9 rows selected SQL > 不要混淆keep内（first、last）外（ min、max或者其他）： min是可以对应last的 max是可以对应first的 SQL > select id,mc,sl, 2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id), 3 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id), 4 min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id), 5 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id) 6 from test 7 ; ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTORDE MAX(MC)KEEP(DENSE_RANKLASTORDE -- ------------------ -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ 1 111 1 111 222 555 666 1 222 1 111 222 555 666 1 333 2 111 222 555 666 1 555 3 111 222 555 666 1 666 3 111 222 555 666 2 111 1 111 222 333 555 2 222 1 111 222 333 555 2 333 2 111 222 333 555 2 555 2 111 222 333 555 9 rows selected SQL > select id,mc,sl, 2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id), 3 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id), 4 min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id), 5 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id) 6 from test 7 ; ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKFIRSTORD MIN(MC)KEEP(DENSE_RANKLASTORDE MAX(MC)KEEP(DENSE_RANKLASTORDE -- ------------------ -------------------- ------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ 1 111 1 111 222 555 666 1 222 1 111 222 555 666 1 333 2 111 222 555 666 1 555 3 111 222 555 666 1 666 3 111 222 555 666 2 111 1 111 222 333 555 2 222 1 111 222 333 555 2 333 2 111 222 333 555 2 555 2 111 222 333 555 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id)：id等于1的数量最小的（DENSE_RANK first ）为 1 111 1 1 222 1 在这个结果中取min(mc) 就是111 max(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id) 取max(mc) 就是222； min(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)：id等于1的数量最大的（DENSE_RANK first ）为 1 555 3 1 666 3 在这个结果中取min(mc) 就是222，取max(mc)就是666 详细讲述看这些地址： http: //zhouwf0726.itpub.net /post / 9689 / 247171 http: //zhouwf0726.itpub.net /post / 9689 / 247175 更多讨论看以下地址： http: //www.itpub.net /showthread.php?s =&threadid = 608107 &perpage = 10 &pagenumber = 1 /* ****************分析函数的计算顺序问题************ */ 有些人对oracle分析函数中select over(partition by col1 order by col2) from test order by ...关于partition by 和 组内order by以及最后的order by的执行顺序产生疑惑。 http: //www.itpub.net /showthread.php?s =&threadid = 732957 &perpage = 10 &pagenumber = 1 over 中的partition为分组， order by是视窗内排序, 先执行 partition 然后order by 如 partition by col_a order by col_b 的执行排序效果类似于order by col_a, col_b 这样的排序效果，如果再在最后加order by，是在前边分组排序的结果基础上进行排序。 SQL > create table test(id varchar2( 20)); Table created SQL > insert into test values( ' 1 '); 1 row inserted SQL > insert into test values( ' 1 '); 1 row inserted SQL > insert into test values( ' 8 '); 1 row inserted SQL > insert into test values( ' 5 '); 1 row inserted SQL > insert into test values( ' 5 '); 1 row inserted SQL > commit; Commit complete SQL > select * from test; ID -- ------------------ 1 1 8 5 5 1.按照id排序: SQL > select row_number() over( order by id),id,rownum from test; ROW_NUMBER() OVER(ORDERBYID) ID ROWNUM -- ------------------------- -------------------- ---------- 1 1 1 2 1 2 3 5 5 4 5 4 5 8 3 2.组内(没有分组就是所有数据1组)按照id排序,最后order by在组内排序基础上按照rownum排序: SQL > select row_number() over( order by id),id,rownum from test order by rownum; ROW_NUMBER() OVER(ORDERBYID) ID ROWNUM -- ------------------------- -------------------- ---------- 1 1 1 2 1 2 5 8 3 4 5 4 3 5 5 3.按照rownum排序: SQL > select row_number() over( order by rownum),id,rownum from test; ROW_NUMBER() OVER(ORDERBYROWNUM ID ROWNUM -- ---------------------------- -------------------- ---------- 1 1 1 2 1 2 3 8 3 4 5 4 5 5 5 4.按照id分组,组内按照id排序 SQL > select row_number() over(partition by id order by id),id,rownum from test; ROW_NUMBER() OVER(PARTITIONBYID ID ROWNUM -- ---------------------------- -------------------- ---------- 1 1 1 2 1 2 1 5 5 2 5 4 1 8 3 5.按照id分组,组内按照rownum(这个是早已经出来的结构)排序: SQL > select row_number() over(partition by id order by rownum),id,rownum from test; ROW_NUMBER() OVER(PARTITIONBYID ID ROWNUM -- ---------------------------- -------------------- ---------- 1 1 1 2 1 2 1 5 4 2 5 5 1 8 3 oracle在提取数据库的时候是按over(partition by ... order by ...)这个里边的order by后边的字段的一个个distinct值取出数据的。 SQL > select * from t; A B C D -- -------- ---------- ---------- ---------- 1 111 G 87 1 111 G 87 1 222 G 85 1 222 G 86 2 111 G 80 2 111 G 80 2 222 G 81 2 222 G 80 8 rows selected 只有partition by a， distinct a有2个值1和2：分2次提取数据 为1的提取一次，4条a值相同，4条平均86. 25 为2的提取一次，4条a值相同，4条平均80. 25 SQL > select a,b,c, avg(d) over(partition by a ),d from t; A B C AVG(D) OVER(PARTITIONBYA) D -- -------- ---------- ---------- ------------------------ ---------- 1 111 G 86.25 87 1 111 G 86.25 87 1 222 G 86.25 85 1 222 G 86.25 86 2 111 G 80.25 80 2 111 G 80.25 80 2 222 G 80.25 81 2 222 G 80.25 80 8 rows selected partition by a， order by b， distinct a，b有4个值： 1 -- -111 1 -- -222 2 -- -111 2 -- -222 分四次提取数据： 1 -- -111：取出2条，a=1的2条取平均87 1 -- -222：取出2条，a=1的4条取平均86.25 2 -- -111：取出2条，a=2的2条取平均80 2 -- -222：取出2条，a=2的4条取平均80.25 SQL > select a,b,c, avg(d) over(partition by a order by b ),d from t; A B C AVG(D) OVER(PARTITIONBYAORDERBY D -- -------- ---------- ---------- ------------------------------ ---------- 1 111 G 87 87 1 111 G 87 87 1 222 G 86.25 85 1 222 G 86.25 86 2 111 G 80 80 2 111 G 80 80 2 222 G 80.25 81 2 222 G 80.25 80 8 rows selected SQL > /* ***************一个综合实例************ */ http: //www.itpub.net /showthread.php?s =&postid = 7237412#post7237412 行列拆分问题 表A数据 起始id 终止ID 面额 890001 890009 20 891001 891007 30 ....... 插入B表 ID 面额 890001 20 890002 20 890003 20 890004 20 890005 20 890006 20 890007 20 890008 20 890009 20 891001 30 891002 30 891003 30 891004 30 891005 30 891006 30 891007 30 ........ 我现在是通过pl /sql过程实现,有没有简便的办法,一条sql语句解决? /* ******************************************************* */ SQL > create table test(s_id varchar2( 20),e_id varchar2( 20),je number( 18)); Table created SQL > insert into test values( ' 890001 ', ' 890009 ', 20); 1 row inserted SQL > insert into test values( ' 891001 ', ' 891007 ', 30); 1 row inserted SQL > insert into test values( ' 892001 ', ' 892022 ', 50); 1 row inserted SQL > insert into test values( ' 893001 ', ' 893008 ', 60); 1 row inserted SQL > commit; Commit complete SQL > select * from test; S_ID E_ID JE -- ------------------ -------------------- ------------------- 890001 890009 20 891001 891007 30 892001 892022 50 893001 893008 60 SQL > SQL > SELECT S_ID +ROWNUM -weight,JE FROM ( 2 select S_ID,RN,E_RN,JE,lag(E_RN, 1, 0) over( order by rownum) + 1 weight from( 3 SELECT S_ID,rownum rn, sum(E_ID -S_ID + 1) over( order by rownum) E_RN,JE FROM TEST 4 ) 5 ) 6 start with rn = 1 CONNECT BY ROWNUM <=e_rn; S_ID +ROWNUM -WEIGHT JE -- ---------------- ------------------- 890001 20 890002 20 890003 20 890004 20 890005 20 890006 20 890007 20 890008 20 890009 20 891001 30 891002 30 891003 30 891004 30 891005 30 891006 30 891007 30 892001 50 892002 50 892003 50 892004 50 S_ID +ROWNUM -WEIGHT JE -- ---------------- ------------------- 892005 50 892006 50 892007 50 892008 50 892009 50 892010 50 892011 50 892012 50 892013 50 892014 50 892015 50 892016 50 892017 50 892018 50 892019 50 892020 50 892021 50 892022 50 893001 60 893002 60 893003 60 S_ID +ROWNUM -WEIGHT JE -- ---------------- ------------------- 893004 60 893005 60 893006 60 893007 60 893008 60 46 rows selected SQL >
扩展GROUP BY研究 http://www.itpub.net/viewthread.php?tid=998611&extra=page%3D2%26amp%3Bfilter%3Ddigest 0.概述 本文主要介绍查询中扩展group by子句的基本用法，同时简要解析工作的原理。 主要包括如下内容： ( 1). 准备 ( 2). GROUP BY ( 3). ROLLUP ( 4). CUBE ( 5). GROUPING SETS ( 6). GROUPING()函数 ( 7). grouping_id()函数 ( 8). group_id()函数 1. 准备 创建一个查询样例表，下面的例子都基于此表。 CREATE TABLE egb_tab( c1 VARCHAR2( 10), c2 VARCHAR2( 10), c3 VARCHAR2( 10), c4 VARCHAR2( 10)); INSERT INTO egb_tab VALUES( ' x ', ' a ', ' 1 ', ' xa1 '); INSERT INTO egb_tab VALUES( ' x ', ' a ', ' 2 ', ' xa2 '); INSERT INTO egb_tab VALUES( ' x ', ' a ', ' 2 ', ' xa2 '); INSERT INTO egb_tab VALUES( ' x ', ' a ', ' 3 ', ' xa3 '); INSERT INTO egb_tab VALUES( ' x ', ' a ', ' 3 ', ' xa3 '); INSERT INTO egb_tab VALUES( ' x ', ' a ', ' 3 ', ' xa3 '); INSERT INTO egb_tab VALUES( ' x ', ' b ', ' 4 ', ' xb4 '); INSERT INTO egb_tab VALUES( ' x ', ' b ', ' 4 ', ' xb4 '); INSERT INTO egb_tab VALUES( ' x ', ' b ', ' 5 ', ' xb4 '); INSERT INTO egb_tab VALUES( ' x ', ' c ', ' 6 ', ' xc6 '); INSERT INTO egb_tab VALUES( ' x ', ' c ', ' 6 ', ' xc6 '); -- ------------------------------------------------------ INSERT INTO egb_tab VALUES( ' y ', ' a ', ' 1 ', ' xa1 '); INSERT INTO egb_tab VALUES( ' y ', ' a ', ' 1 ', ' xa1 '); INSERT INTO egb_tab VALUES( ' y ', ' a ', ' 2 ', ' xa1 '); INSERT INTO egb_tab VALUES( ' y ', ' a ', ' 2 ', ' xa1 '); INSERT INTO egb_tab VALUES( ' y ', ' a ', ' 3 ', ' xa1 '); INSERT INTO egb_tab VALUES( ' y ', ' b ', ' 4 ', ' xb2 '); INSERT INTO egb_tab VALUES( ' y ', ' b ', ' 4 ', ' xb2 '); INSERT INTO egb_tab VALUES( ' y ', ' b ', ' 5 ', ' xb2 '); INSERT INTO egb_tab VALUES( ' y ', ' c ', ' 6 ', ' xa1 '); INSERT INTO egb_tab VALUES( ' y ', ' c ', ' 7 ', ' xb2 '); INSERT INTO egb_tab VALUES( ' y ', ' c ', ' 7 ', ' xb2 '); INSERT INTO egb_tab VALUES( ' y ', ' d ', ' 8 ', ' xb2 '); INSERT INTO egb_tab VALUES( ' y ', ' d ', ' 9 ', ' xa1 '); -- ------------------------------------------------------ INSERT INTO egb_tab VALUES( ' z ', ' a ', ' 1 ', ' xa5 '); INSERT INTO egb_tab VALUES( ' z ', ' a ', ' 2 ', ' xa5 '); INSERT INTO egb_tab VALUES( ' z ', ' f ', ' 6 ', ' xa5 '); INSERT INTO egb_tab VALUES( ' z ', ' f ', ' 6 ', ' xa3 '); INSERT INTO egb_tab VALUES( ' z ', ' f ', ' 7 ', ' xa4 '); COMMIT; SELECT * FROM egb_tab; C1 C2 C3 C4 -- ---------- x a 1 xa1 x a 2 xa2 x a 2 xa2 x a 3 xa3 x a 3 xa3 x a 3 xa3 x b 4 xb4 x b 4 xb4 x b 5 xb4 x c 6 xc6 x c 6 xc6 y a 1 xa1 y a 1 xa1 y a 2 xa1 y a 2 xa1 y a 3 xa1 y b 4 xb2 y b 4 xb2 y b 5 xb2 y c 6 xa1 y c 7 xb2 y c 7 xb2 y d 8 xb2 y d 9 xa1 z a 1 xa5 z a 2 xa5 z f 6 xa5 z f 6 xa3 z f 7 xa4 2. GROUP BY 单独使用group by统计方式只有一种，即按group by后的所有列进行一次统计。 注意： GROUP BY，以及 ROLLUP,CUBE, GROUPING SETS 后面的参数， 不必都出现在查询列中，只要保证查询列中的参数是其子集便可。 比如下面的例子中， SELECT 中不必选择c1，c2，只选择c3是可以的。 SELECT c1, c2, c3, COUNT(c4) cnt FROM egb_tab GROUP BY c1, c2, c3 ORDER BY c1, c2, c3; C1 C2 C3 CNT -- ---------- x a 1 1 x a 2 2 x a 3 3 x b 4 2 x b 5 1 x c 6 2 y a 1 2 y a 2 2 y a 3 1 y b 4 2 y b 5 1 y c 6 1 y c 7 2 y d 8 1 y d 9 1 z a 1 1 z a 2 1 z f 6 2 z f 7 1 3. ROLLUP 若rollup的参数个数为n,则统计的方式有n +1种。 假设 ROLLUP(p1,p2,...,p(n - 1),p(n))，则分别按 p1,p2,..,p(n - 1),p(n) p1,p2,...p(n - 1) ... ... p1 NULL 分组统计，其中 NULL 表示不分组。 例子： SELECT c1, c2, c3, COUNT(c4) cnt FROM egb_tab GROUP BY ROLLUP(c1, c2, c3) ORDER BY c1, c2, c3; C1 C2 C3 CNT -- ---------- x a 1 1 x a 2 2 x a 3 3 x a 6 x b 4 2 x b 5 1 x b 3 x c 6 2 x c 2 x 11 y a 1 2 y a 2 2 y a 3 1 y a 5 y b 4 2 y b 5 1 y b 3 y c 6 1 y c 7 2 y c 3 y d 8 1 y d 9 1 y d 2 y 13 z a 1 1 z a 2 1 z a 2 z f 6 2 z f 7 1 z f 3 z 5 29 <=> 以下查询union all(需要剔除中间的order by) ( 1)按c1,c2,c3分组统计 SELECT c1, c2, c3, COUNT(c4) cnt FROM egb_tab GROUP BY c1, c2, c3 ORDER BY c1, c2, c3; C1 C2 C3 CNT -- ---------- x a 1 1 x a 2 2 x a 3 3 x b 4 2 x b 5 1 x c 6 2 y a 1 2 y a 2 2 y a 3 1 y b 4 2 y b 5 1 y c 6 1 y c 7 2 y d 8 1 y d 9 1 z a 1 1 z a 2 1 z f 6 2 z f 7 1 ( 2)按c1,c2分组统计 SELECT c1, c2, NULL c3, COUNT(c4) cnt FROM egb_tab GROUP BY c1, c2, NULL ORDER BY c1, c2, NULL; C1 C2 C3 CNT -- ---------- x a 6 x b 3 x c 2 y a 5 y b 3 y c 3 y d 2 z a 2 z f 3 ( 3)按c1分组统计 SELECT c1, NULL c2, NULL c3, COUNT(c4) cnt FROM egb_tab GROUP BY c1, NULL, NULL ORDER BY c1, NULL, NULL; C1 C2 C3 CNT -- ----------- x 11 y 13 z 5 ( 4)不分组统计 SELECT NULL c1, NULL c2, NULL c3, COUNT(c4) cnt FROM egb_tab GROUP BY NULL, NULL, NULL ORDER BY NULL, NULL, NULL; C1 C2 C3 CNT -- ----------- 29 4. CUBE 若cube参数的个数为n,则统计的方式有2 ^n种(表示2的n次方)。 假设 CUBE(p1,p2,...,p(n - 1),p(n))，则分别按： 从p1,p2,...,p(n - 1),p(n)中: 选1个分组，组合为C(n, 1), 选2个分组，组合为C(n, 2), ... 选n -1个分组，组合为C(n,n - 1), 选n个分组，组合为C(n,n) 不分组，相当于C(n, 0) 进行统计，故整个统计种数为：C(n, 0) + C(n, 1) + C(n, 2) + ... + C(n,n - 1) + C(n,n) = 2 ^n。 例子： SELECT c1,c2,c3, COUNT(c4) cnt FROM egb_tab GROUP BY CUBE(c1,c2,c3) ORDER BY c1,c2,c3; <=>以下查询union all(需要剔除中间的order by) ( 1)不分组统计 SELECT NULL c1, NULL c2, NULL c3, COUNT(c4) FROM egb_tab GROUP BY NULL, NULL, NULL ORDER BY NULL, NULL, NULL C1 C2 C3 CNT -- ----------- 29 ( 2)按c1分组统计 SELECT c1, NULL c2, NULL c3, COUNT(c4) cnt FROM egb_tab GROUP BY c1, NULL, NULL ORDER BY c1, NULL, NULL C1 C2 C3 CNT -- ------------- x 11 y 13 z 5 ( 3)按c2分组统计 SELECT NULL c1, c2, NULL c3, COUNT(c4) cnt FROM egb_tab GROUP BY NULL, c2, NULL ORDER BY NULL, c2, NULL C1 C2 C3 CNT -- -------------- a 13 b 6 c 5 d 2 f 3 ( 4)按c3分组统计 SELECT NULL c1, NULL c2, c3, COUNT(c4) cnt FROM egb_tab GROUP BY NULL, NULL, c3 ORDER BY NULL, NULL, c3 C1 C2 C3 CNT
本文转自suifei博客园博客，原文链接http://www.cnblogs.com/Chinasf/archive/2010/01/30/1660259.html，如需转载请自行联系原作者