Oracle中分组查询时keep的使用[转][

最新推荐文章于 2024-11-04 10:13:26 发布

cuipa2654

最新推荐文章于 2024-11-04 10:13:26 发布

阅读量119

点赞数

文章标签：数据库

for example:

15:18:59 SQL> create table t1 (a int, b int);

表已创建。

已用时间: 00: 00: 00.15
15:18:59 SQL>
15:18:59 SQL> insert into t1 values (1,5);

已创建 1 行。

已用时间: 00: 00: 00.16
15:18:59 SQL> insert into t1 values (1,3);

已创建 1 行。

已用时间: 00: 00: 00.16
15:18:59 SQL> insert into t1 values (1,4);

已创建 1 行。

已用时间: 00: 00: 00.15
15:18:59 SQL> insert into t1 values (2,1);

已创建 1 行。

已用时间: 00: 00: 00.16
15:18:59 SQL> insert into t1 values (2,3);

已创建 1 行。

已用时间: 00: 00: 00.00
15:18:59 SQL>
15:18:59 SQL> SELECT
15:18:59 2 max(b) KEEP (DENSE_RANK LAST ORDER BY (a) desc) AS last,
15:18:59 3 max(b) KEEP (DENSE_RANK FIRST ORDER BY (a) desc) AS first
15:18:59 4 FROM t1;

LAST FIRST
---------- ----------
5 3

已用时间: 00: 00: 00.00

SQL含义：

从DENSE_RANK取出的结果集中取出最小的一项(以a排序)，可能取出多行，因为a值可能相同。再从这些行中取出b字段最大的一行。
选出
以字段a分组，最小一组中（LAST 降序）所有记录中b的最大值
和
以字段a分组，最大一组中（FRIST 降序）所有记录中b的最大值

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