分析函数keep用法，实现求最大最小值

WITH m AS (
SELECT 22 AS ID,'a' AS str1,'b' AS str2,1 AS index_no from dual
UNION ALL
SELECT 22,'aa','bb',2 FROM dual
UNION ALL
SELECT 22,'aaa','bbb',3 FROM dual
UNION ALL
SELECT 55,'am','bm',1 FROM dual
UNION ALL
SELECT 55,'aam','bbm',2 FROM dual
UNION ALL
SELECT 55,'aaam','bbbm',3 FROM dual
UNION ALL
SELECT 55,'wert','werty',4 FROM dual
)
SELECT * FROM m;

示例如上：根据ID分组后，取出index_no最小的字段str1，并取出index_no最大的字段str2。
最终需要的结果如下：
22  a  bbb
55  am werty              

解决方法如下：

点击(此处)折叠或打开

1. SQL> WITH m AS
   
2.   2 (SELECT 22 AS ID, 'a' AS str1, 'b' AS str2, 1 AS index_no
   
3.   3 from dual
   
4.   4 UNION ALL
   
5.   5 SELECT 22, 'aa', 'bb', 2
   
6.   6 FROM dual
   
7.   7 UNION ALL
   
8.   8 SELECT 22, 'aaa', 'bbb', 3
   
9.   9 FROM dual
   
10.  10 UNION ALL
    
11.  11 SELECT 55, 'am', 'bm', 1
    
12.  12 FROM dual
    
13.  13 UNION ALL
    
14.  14 SELECT 55, 'aam', 'bbm', 2
    
15.  15 FROM dual
    
16.  16 UNION ALL
    
17.  17 SELECT 55, 'aaam', 'bbbm', 3
    
18.  18 FROM dual
    
19.  19 UNION ALL
    
20.  20 SELECT 55, 'wert', 'werty', 4
    
21.  21 FROM dual)
    
22.  22 SELECT id,
    
23.  23 min(str1) keep(dense_rank first order by index_no) str1,
    
24.  24 max(str2) keep(dense_rank last order by index_no) str2
    
25.  25 from m
    
26.  26 group by id;
    
27. 
    
28.         ID STR1 STR2
    
29. ---------- ---- -----
    
30.         22 a bbb
    
31.         55 am werty
    
32. 
    
33. SQL>

查阅官方文档，有关first语法如下：


详细可查看官方文档“SQL Language Refernece”中5 funcions下first函数用法
上例中，如果要将m中所有列都显示出来，并显示最大最小值，可将代码改写如下：

点击(此处)折叠或打开

1. SQL> WITH m AS
   
2.   2 (SELECT 22 AS ID, 'a' AS str1, 'b' AS str2, 1 AS index_no
   
3.   3 from dual
   
4.   4 UNION ALL
   
5.   5 SELECT 22, 'aa', 'bb', 2
   
6.   6 FROM dual
   
7.   7 UNION ALL
   
8.   8 SELECT 22, 'aaa', 'bbb', 3
   
9.   9 FROM dual
   
10.  10 UNION ALL
    
11.  11 SELECT 55, 'am', 'bm', 1
    
12.  12 FROM dual
    
13.  13 UNION ALL
    
14.  14 SELECT 55, 'aam', 'bbm', 2
    
15.  15 FROM dual
    
16.  16 UNION ALL
    
17.  17 SELECT 55, 'aaam', 'bbbm', 3
    
18.  18 FROM dual
    
19.  19 UNION ALL
    
20.  20 SELECT 55, 'wert', 'werty', 4
    
21.  21 FROM dual)
    
22.  22 SELECT m.*,
    
23.  23 min(str1) keep(dense_rank first order by index_no) over(partition by id) min_str1,
    
24.  24 max(str2) keep(dense_rank last order by index_no) over(partition by id) max_str2
    
25.  25 from m;
    
26. 
    
27.         ID STR1 STR2 INDEX_NO MIN_ MAX_S
    
28. ---------- ---- ----- ---------- ---- -----
    
29.         22 aaa bbb 3 a bbb
    
30.         22 aa bb 2 a bbb
    
31.         22 a b 1 a bbb
    
32.         55 aaam bbbm 3 am werty
    
33.         55 aam bbm 2 am werty
    
34.         55 wert werty 4 am werty
    
35.         55 am bm 1 am werty
    
36. 
    
37. 已选择7行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21251711/viewspace-1757904/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21251711/viewspace-1757904/