我有一个包含此数据的表 MOVIE。
MOVIE_ID MOVIE_TITLE CATEGORY SALES_AMT
--------- ------------------------ --------------- ----------
M_0000004 The Boss Baby Animation 2000
M_0000006 Star Wars: The Last Jedi Science Fiction 3000
M_0000007 Get Out Horror 4000
M_0000008 Million Dollar Arm Action 2000
M_0000009 The Conjuring Horror 1000
M_0000012 The Dark Knight Action 3000
我需要基于SALES_AMT
的热门电影数据CATEGORY
所需的结果是:
MOVIE_ID MOVIE_TITLE CATEGORY SALES_AMT
--------- ------------------------ --------------- ----------
M_0000004 The Boss Baby Animation 2000
M_0000006 Star Wars: The Last Jedi Science Fiction 3000
M_0000007 Get Out Horror 4000
M_0000012 The Dark Knight Action 3000
如果我使用GROUP_BY选项,我无法选择MOVIE_ID
和MOVIE_TITLE
select CATEGORY, MAX(SALES_AMT)
from MOVIE
group by CATEGORY
;
尝试使用分析函数和子查询
select movie_id, movie_title, category, sales_amt
from (
select movie_id, movie_title, category, sales_amt,
row_number() over (partition by category order by sales_amt desc) r
from movie
) where r = 1
您可以使用 RANK() 来实现此目的:
SELECT
MOVIE_ID
, MOVIE_TITLE
, CATEGORY
, SALES_AMT
FROM
(
SELECT
MOVIE_ID
, MOVIE_TITLE
, CATEGORY
, SALES_AMT
, RANK() OVER (PARTITION BY CATEGORY ORDER BY SALES_AMT DESC) RNK
FROM MOVIE
) Q
WHERE RNK = 1
如果您希望在结果中看到重复项(销售额相等),否则,请将RANK
替换为 ROW_NUMBER
。
假设您先运行以下命令:
SQL> select deptno, min(sal)
2 from emp
3 group by deptno;
然后想得到有最低工资的员工。 那么这当然失败了:
SQL> select deptno, empno, min(sal)
2 from emp
3 group by deptno;
ORA-00979: not a GROUP BY expression
但是您可以将 KEEP 语法与窗口函数一起使用来提供帮助
SQL> select deptno, min(sal), min(empno)
2 KEEP ( dense_rank FIRST order by sal) empno
3 from emp
4 group by deptno
5 /
DEPTNO MIN(SAL) EMPNO
---------- ---------- ----------
10 1300 7934
20 800 7369
30 950 7900
其中读作"EMPNO 7934 是薪水为 1300 的人"