SQL> WITH a AS ( 2 SELECT '1' dept, 20 sal FROM DUAL UNION ALL 3 SELECT '1' dept, 15 sal FROM DUAL UNION ALL 4 SELECT '2' dept, 39 sal FROM DUAL UNION ALL 5 SELECT '3' dept, 20 sal FROM DUAL UNION ALL 6 SELECT '2' dept, 18 sal FROM DUAL UNION ALL 7 SELECT '1' dept, 22 sal FROM DUAL UNION ALL 8 SELECT '3' dept, 10 sal FROM DUAL 9 ) 10 SELECT m.dept, 11 m.sal 12 FROM (SELECT a.dept, 13 SUM(a.sal) sal, 14 RANK() over(ORDER BY SUM(a.sal) DESC) cnt 15 FROM a 16 GROUP BY a.dept) m 17 WHERE m.cnt = 1;DE SAL -- ---------- 1 57 2 57
RANK() over(ORDER BY SUM(a.sal) DESC) cnt 这一句是什么意思 可否用max函数来写呢
SQL> WITH ta AS ( 2 SELECT '1' dept, 20 sal FROM DUAL UNION ALL 3 SELECT '1' dept, 15 sal FROM DUAL UNION ALL 4 SELECT '2' dept, 39 sal FROM DUAL UNION ALL 5 SELECT '3' dept, 20 sal FROM DUAL UNION ALL 6 SELECT '2' dept, 18 sal FROM DUAL UNION ALL 7 SELECT '1' dept, 22 sal FROM DUAL UNION ALL 8 SELECT '3' dept, 10 sal FROM DUAL) 9 ,tb as( 10 select dept,sum(sal) sal 11 from ta 12 group by dept) 13 select * from tb c 14 where c.sal in ( 15 select max(sal) from tb) 16 /
DEPT SAL ---- ---------- 1 57 2 57
看看结果就明白了, SQL> WITH a AS ( 2 SELECT '1' dept, 20 sal FROM DUAL UNION ALL 3 SELECT '1' dept, 15 sal FROM DUAL UNION ALL 4 SELECT '2' dept, 39 sal FROM DUAL UNION ALL 5 SELECT '3' dept, 20 sal FROM DUAL UNION ALL 6 SELECT '2' dept, 18 sal FROM DUAL UNION ALL 7 SELECT '1' dept, 22 sal FROM DUAL UNION ALL 8 SELECT '3' dept, 10 sal FROM DUAL 9 ) 10 SELECT a.dept, 11 SUM(a.sal) sal, 12 RANK() over(ORDER BY SUM(a.sal) DESC) cnt 13 FROM a 14 GROUP BY a.dept;DE SAL CNT -- ---------- ---------- 1 57 1 2 57 1 3 30 3RANK()会根据SUM(a.sal)生成从1开始的序号,如果两个SUM(a.sal)相同,则序号相同。 使用max也能写,不过得用子查询,像2楼那样,效率不高,表会扫描2次。
--RANK() over(ORDER BY SUM(a.sal) DESC) cnt --rank()是一个函数,它根据sum(sal)的值返回一个排序值,相同的sum返回相同的值, --但是会留下空位: SELECT a.dept, SUM(a.sal) sal, RANK() over(ORDER BY SUM(a.sal) DESC) cnt FROM a GROUP BY a.dept /
where sal=(select max(sal) from 表A)
m.sal
FROM (SELECT a.dept,
a.sal,
row_number() over(PARTITION BY dept ORDER BY sal DESC) cnt
FROM a) m
WHERE m.cnt = 1
where not exist
(select 1 from 表A a where t.sal<a.sal)
我要的结果是
表A
dept sal
1 20
1 15
2 39
3 20
2 18
1 22
3 10
先求出每个部门的sal的总和
部门1的总和57
部门2的总和57
部门3的综合30
然后求出总额和最高的
1 57
2 57
怎么写,求sql
2 SELECT '1' dept, 20 sal FROM DUAL UNION ALL
3 SELECT '1' dept, 15 sal FROM DUAL UNION ALL
4 SELECT '2' dept, 39 sal FROM DUAL UNION ALL
5 SELECT '3' dept, 20 sal FROM DUAL UNION ALL
6 SELECT '2' dept, 18 sal FROM DUAL UNION ALL
7 SELECT '1' dept, 22 sal FROM DUAL UNION ALL
8 SELECT '3' dept, 10 sal FROM DUAL
9 )
10 SELECT m.dept,
11 m.sal
12 FROM (SELECT a.dept,
13 SUM(a.sal) sal,
14 RANK() over(ORDER BY SUM(a.sal) DESC) cnt
15 FROM a
16 GROUP BY a.dept) m
17 WHERE m.cnt = 1;DE SAL
-- ----------
1 57
2 57
这一句是什么意思
可否用max函数来写呢
SQL> WITH ta AS (
2 SELECT '1' dept, 20 sal FROM DUAL UNION ALL
3 SELECT '1' dept, 15 sal FROM DUAL UNION ALL
4 SELECT '2' dept, 39 sal FROM DUAL UNION ALL
5 SELECT '3' dept, 20 sal FROM DUAL UNION ALL
6 SELECT '2' dept, 18 sal FROM DUAL UNION ALL
7 SELECT '1' dept, 22 sal FROM DUAL UNION ALL
8 SELECT '3' dept, 10 sal FROM DUAL)
9 ,tb as(
10 select dept,sum(sal) sal
11 from ta
12 group by dept)
13 select * from tb c
14 where c.sal in (
15 select max(sal) from tb)
16 /
DEPT SAL
---- ----------
1 57
2 57
SQL> WITH a AS (
2 SELECT '1' dept, 20 sal FROM DUAL UNION ALL
3 SELECT '1' dept, 15 sal FROM DUAL UNION ALL
4 SELECT '2' dept, 39 sal FROM DUAL UNION ALL
5 SELECT '3' dept, 20 sal FROM DUAL UNION ALL
6 SELECT '2' dept, 18 sal FROM DUAL UNION ALL
7 SELECT '1' dept, 22 sal FROM DUAL UNION ALL
8 SELECT '3' dept, 10 sal FROM DUAL
9 )
10 SELECT a.dept,
11 SUM(a.sal) sal,
12 RANK() over(ORDER BY SUM(a.sal) DESC) cnt
13 FROM a
14 GROUP BY a.dept;DE SAL CNT
-- ---------- ----------
1 57 1
2 57 1
3 30 3RANK()会根据SUM(a.sal)生成从1开始的序号,如果两个SUM(a.sal)相同,则序号相同。
使用max也能写,不过得用子查询,像2楼那样,效率不高,表会扫描2次。
--RANK() over(ORDER BY SUM(a.sal) DESC) cnt
--rank()是一个函数,它根据sum(sal)的值返回一个排序值,相同的sum返回相同的值,
--但是会留下空位:
SELECT a.dept,
SUM(a.sal) sal,
RANK() over(ORDER BY SUM(a.sal) DESC) cnt
FROM a
GROUP BY a.dept
/
DEPT SAL CNT
---- ---------- ----------
1 57 1
2 57 1--
3 30 3--