xml地图|网站地图|网站标签 [设为首页] [加入收藏]
当前位置: www8029com > 澳门新葡8522最新网站 > 正文

澳门新葡8522最新网站:最大值对应时间

时间:2019-08-26 17:36来源:澳门新葡8522最新网站
先创建Students表 select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOBField)) fromTableName; CREATE TABLE [dbo].[Students]( [Id] [int] IDENTITY(1,1) NOT NULL, [age] [int] NULL, [name] [nvarchar](50) NULL, [addTime] [datetime]  NULL ) ON

先创建Students表

select UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(BLOBField)) from TableName;

CREATE TABLE [dbo].[Students](
[Id] [int] IDENTITY(1,1) NOT NULL,
[age] [int] NULL,
[name] [nvarchar](50) NULL,
[addTime] [datetime]  NULL
) ON [PRIMARY]

 

 

澳门新葡8522最新网站 1澳门新葡8522最新网站 2出处:

插入几条测试数据

oracle分析函数 

INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (22, N'李四', '2015-04-08 01:00:00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (8, N'李四', '2017-05-03 00:00:00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (98, N'李四', '2017-10-03 00:00:00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (34, N'张三', '2016-09-08 00:00:00.000')
INSERT [dbo].[Students] ([age], [name], [addTime]) VALUES (45, N'张三','2011-05-08 00:00:00.000')
INSERT [dbo].[Students] ( [age], [name], [addTime]) VALUES (5, N'张三', '2014-04-01 00:00:00.000')

=========================================================== 
作者: zhouwf0726()
发表于:2006.07.25 12:51
分类: oracle开发 
出处:
--------------------------------------------------------------- 

第一种写法:

oracle分析函数--SQL*PLUS环境
--1、GROUP BY子句 

  这种写法用到了窗口函数,窗口函数的行为描述出现在函数的OVER子句中,并涉及多个元素,3个核心元素分别是:分区,排序和框架

--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));

select distinct name,
maxAge, max(case maxAgenum when 1 then addtime else '' end) over(partition by name) maxAddTime ,
minage,max(case minAgenum when 1 then addtime else '' end) over(partition by name) minAddTime
from (
select name,addtime,
max(age) over(partition by name) maxAge,
min(age) over(partition by name) minAge,
RANK() over(partition by name order by age desc) maxAgeNum ,
RANK() over(partition by name order by age ) minAgeNum from students
) s

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

with s as
(
select name,max(age) maxAge,min(age) minAge from students
group by name
)
select name,max(maxAge) maxAge,max(maxAgeTime) maxAgeTime,max(minAge) minAge,max(minAgeTime) minAgeTime from (
select ss.name,s.maxAge,ss.addTime maxAgeTime,0 minAge, '' minAgeTime from students ss inner join s on ss.name=s.name and ss.age=s.maxAge
union all
select ss.name,0 maxAge , '' maxAgeTime,s.minAge minAge,ss.addTime minAgeTime from students ss inner join s on ss.name=s.name and ss.age=s.minAge
) a group by name

--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 )

 澳门新葡8522最新网站 3

等效于

 

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 
)
*/

澳门新葡8522最新网站 4

--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);

 

等效于

澳门新葡8522最新网站 5

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;

select name,d '日期',maxAge,maxAddTime,minage,minAddTime from
(
          select distinct name,d,
          maxAge, max(case maxAgenum when 1 then addtime else '' end) over(partition by name,d) maxAddTime ,
          minage,max(case minAgenum when 1 then addtime else '' end) over(partition by name,d) minAddTime
          from
         (
              select name,addtime,DAY(addtime) d,
              max(age) over(partition by name,DAY(addtime)) maxAge,
              min(age) over(partition by name,DAY(addtime)) minAge,
              RANK() over(partition by name,DAY(addtime) order by age desc) maxAgeNum ,
              RANK() over(partition by name,DAY(addtime) order by age ) minAgeNum from students where addtime>='2017-5-1' and addtime<'2017-6-1'
         ) s
) a order by name,d

/*--------理解cube
select a, b, c, sum( d ) from t
group by cube( a, b, c)

澳门新葡8522最新网站 6

等效于

 

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 

*/

--给出一个例子再次理解分析函数

/*********************************************************************************************

问题提出:

一个高级SQL语句问题 
假设有一张表,A和B字段都是NUMBER,
A B
1 2
2 3
3 4

有这样一些数据
现在想用一条SQL语句,查询出这样的数据
1-》2-》3—》4
就是说,A和B的数据表示一种连接的关系,现在想通过A的一个值,去查询A所对应的B值,直到B为NULL为止,
不知道这个SQL语句怎么写?请教高手!谢谢

*********************************************************************************************/

--以下是利用分析函数的一个简单解答:
--start with connect by可以参考

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

详细讲述看这些地址:

更多讨论看以下地址:

/*****************分析函数的计算顺序问题*************/

有些人对oracle分析函数中select over(partition by col1 order by col2) from test order by ...关于partition by 和 组内order by以及最后的order by的执行顺序产生疑惑。

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>

/****************一个综合实例*************/

行列拆分问题 

表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> 

 

 

澳门新葡8522最新网站 7澳门新葡8522最新网站 8扩展GROUP BY研究

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

 

 

编辑:澳门新葡8522最新网站 本文来源:澳门新葡8522最新网站:最大值对应时间

关键词: www8029com