SELECT DECODE(ORG.ORG_NAME, '', '合计', ORG.ORG_NAME) 地市局,
B.CREATE_COUNT 总数
FROM (SELECT A.ORGANIZATION_ID,
COUNT(1) CREATE_COUNT
FROM (SELECT F.CREATE_TIME,
(SELECT max(ORG.ORG_ID) keep(dense_rank first order by DECODE(ORG.ORG_TYPE, 4, 1))
FROM TOP_ORGANIZATION ORG
START WITH ORG.ORG_ID = FR.ORGANIZATION_ID
CONNECT BY ORG.ORG_ID = PRIOR ORG.PARENT_ORG_ID) ORGANIZATION_ID,
F.TASK_STATUS_DESCRIBE
FROM SP_PD_FAULT_REPORT FR, SP_PD_FAULT F
WHERE F.CREATE_TIME BETWEEN
TO_DATE('2017-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_DATE('2017-05-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND F.ID = FR.FAULT_ID) A
GROUP BY ROLLUP(A.ORGANIZATION_ID)) B,
TOP_ORGANIZATION ORG
WHERE B.ORGANIZATION_ID = ORG.ORG_ID(+)
AND lnnvl(ORG.ORG_TYPE != 4)
ORDER BY ORG.SORT_NO ASC;
Sql代码
- -- emp表的数据
- SQL> SELECT t.empno,
- 2 t.ename,
- 3 t.mgr,
- 4 t.sal,
- 5 t.deptno
- 6 FROM emp t
- 7 ORDER BY t.sal,
- 8 t.deptno;
- EMPNO ENAME MGR SAL DEPTNO
- ---------- -------------------- ---------- ---------- ----------
- 111 aaa 2222 800 9
- 7369 SMITH 7902 800 20
- 7900 JAMES 7698 950 30
- 7876 ADAMS 7788 1100 20
- 7521 WARD 7698 1250 30
- 7654 MARTIN 7698 1250 30
- 7934 MILLER 7782 1300 10
- 7844 TURNER 7698 1500 30
- 7499 ALLEN 7698 1600 30
- 7782 CLARK 7839 2450 10
- 7698 BLAKE 7839 2850 30
- EMPNO ENAME MGR SAL DEPTNO
- ---------- -------------------- ---------- ---------- ----------
- 7566 JONES 7839 2975 20
- 7788 SCOTT 7566 3000 20
- 7902 FORD 7566 3000 20
- 7839 KING 5000 10
- 222 bbb 3333 5000 40
- -- 1.现在要查询表中工资最高的部门号的最大最小值,工资最低的部门号的最大最小值
- -- 因为是DENSE_RANK，会产生重复数据，使用min,max取一条。
- -- 这个sql没有使用over子句，后面的例子会使用
- SQL> SELECT MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) a,
- 2 MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) b,
- 3 MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) c,
- 4 MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) d
- 5 FROM emp t;
- A B C D
- ---------- ---------- ---------- ----------
- 9 20 10 40
- -- 2.加上over,对每一行记录做计算，看看效果：
- SQL>
- SQL> SELECT t.empno,
- 2 t.ename,
- 3 t.mgr,
- 4 t.sal,
- 5 t.deptno,
- 6 MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER() a,
- 7 MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER() b,
- 8 MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER() c,
- 9 MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER() d
- 10 FROM emp t
- 11 ORDER BY t.sal,
- 12 t.deptno
- 13 ;
- EMPNO ENAME MGR SAL DEPTNO A B C D
- ----- ---------- ----- --------- ------ ---------- ---------- ---------- ----------
- 111 aaa 2222 800.00 9 9 20 10 40
- 7369 SMITH 7902 800.00 20 9 20 10 40
- 7900 JAMES 7698 950.00 30 9 20 10 40
- 7876 ADAMS 7788 1100.00 20 9 20 10 40
- 7521 WARD 7698 1250.00 30 9 20 10 40
- 7654 MARTIN 7698 1250.00 30 9 20 10 40
- 7934 MILLER 7782 1300.00 10 9 20 10 40
- 7844 TURNER 7698 1500.00 30 9 20 10 40
- 7499 ALLEN 7698 1600.00 30 9 20 10 40
- 7782 CLARK 7839 2450.00 10 9 20 10 40
- 7698 BLAKE 7839 2850.00 30 9 20 10 40
- 7566 JONES 7839 2975.00 20 9 20 10 40
- 7788 SCOTT 7566 3000.00 20 9 20 10 40
- 7902 FORD 7566 3000.00 20 9 20 10 40
- 7839 KING 5000.00 10 9 20 10 40
- 222 bbb 3333 5000.00 40 9 20 10 40
- -- 3.下面对每一个mgr求最大(最小)工资的部门号的最大(最小)值
- SQL> SELECT t.empno,
- 2 t.ename,
- 3 t.mgr,
- 4 t.sal,
- 5 t.deptno,
- 6 MIN(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER(PARTITION BY t.mgr) a,
- 7 MAX(t.deptno) KEEP(DENSE_RANK FIRST ORDER BY t.sal) OVER(PARTITION BY t.mgr) b,
- 8 MIN(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER(PARTITION BY t.mgr) c,
- 9 MAX(t.deptno) KEEP(DENSE_RANK LAST ORDER BY t.sal) OVER(PARTITION BY t.mgr) d
- 10 FROM emp t
- 11 ORDER BY t.sal,
- 12 t.deptno
- 13 ;
- EMPNO ENAME MGR SAL DEPTNO A B C D
- ----- ---------- ----- --------- ------ ---------- ---------- ---------- ----------
- 111 aaa 2222 800.00 9 9 9 9 9
- 7369 SMITH 7902 800.00 20 20 20 20 20
- 7900 JAMES 7698 950.00 30 30 30 30 30
- 7876 ADAMS 7788 1100.00 20 20 20 20 20
- 7654 MARTIN 7698 1250.00 30 30 30 30 30
- 7521 WARD 7698 1250.00 30 30 30 30 30
- 7934 MILLER 7782 1300.00 10 10 10 10 10
- 7844 TURNER 7698 1500.00 30 30 30 30 30
- 7499 ALLEN 7698 1600.00 30 30 30 30 30
- 7782 CLARK 7839 2450.00 10 10 10 20 20
- 7698 BLAKE 7839 2850.00 30 10 10 20 20
- 7566 JONES 7839 2975.00 20 10 10 20 20
- 7902 FORD 7566 3000.00 20 20 20 20 20
- 7788 SCOTT 7566 3000.00 20 20 20 20 20
- 7839 KING 5000.00 10 10 10 10 10
- 222 bbb 3333 5000.00 40 40 40 40 40
最近在工作中，碰到一个问题，后来在处理过程中接触到了KEEP，DENSE_RANK分析函数，问题瞬间被简单的解决，否则按照常规写法，肯定会写出嵌套语句，但是网上对这个方面的介绍比较少，现在特整理了一下：
从目前查到的来看：DENSE_RANK是和KEEP结合使用的，网上给出的解释是Returns the row ranked first using DENSE_RANK。我的理解是返回DENSE_RANK函数的第一结果。一般写法是 MIN [ MAX ] (A) KEEP(DENSE_RANK FIRST [ LAST ] ORDER BY B)，这里引用别人说的明的解释一下：
DENSE_RANK
功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。密集的序列返回的时没有间隔的数。
FIRST
功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
LAST
功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
所以默认排序下，FIRST可以理解是取小值，LAST取大值。而前面的MIN或者MAX则是在KEEP的结果集中取某一字段的最大值或最小值。
语文没学好
上面的可能没说清楚，而且网上的相关的资料也少，所以最后给大家看一个完整的SQL语句并解释一下作用，大家自己理解一下吧....
上面的可能没说清楚，而且网上的相关的资料也少，所以最后给大家看一个完整的SQL语句并解释一下作用，大家自己理解一下吧....
表结构大概如下：
| ZGH | WM | RQ |
| A | 1 | 20130101 |
| A | 2 | 20140102 |
| A | 1 | 20120102 |
| B | 3 | 20131001 |
| B | 2 | 20140102 |
| B | 3 | 20121004 |
其中相同ZGH对应的字段WM、RQ值可能有多条，要求从中取出WM值最小的那一条，但可能存在多条，这时要取出其中RQ最小的那一条，以上语句可以实现。
最后取出的值应该是：
| ZGH | WM | RQ |
| A | 1 | 20120102 |
| B | 2 | 20140102 |
7366
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
