如何获取其他列不在GROUP BY子句中的oracle select sql？ [英] How to get other columns with are not in GROUP BY clause in a oracle select sql?

问题描述

我有一个包含此数据的表MOVIE。

  MOVIE_ID MOVIE_TITLE CATEGORY SALES_AMT 
 ------ --- ------------------------ --------------- -------- -  
 M_0000004老板宝贝动画2000 
 M_0000006星球大战：最后的绝地科幻小说3000 
 M_0000007出去恐怖4000 
 M_0000008百万美元手臂动作2000 
 M_0000009魔法可怕1000 
 M_0000012黑暗骑士行动3000 
  

我需要基于热门电影数据关于 CATEGORY

所需的 SALES_AMT 是这样的：

  MOVIE_ID MOVIE_TITLE CATEGORY SALES_AMT 
 --------- ------ ------------------ --------------- ---------- 
 M_0000004老板Baby Animation 2000 
 M_0000006星球大战：最后的绝地科幻小说3000 
 M_0000007出去恐怖4000 
 M_0000012黑暗骑士行动3000 
  

如果我使用GROUP_BY选项，则无法选择 MOVIE_ID 和 MOVIE_TITLE

 从MOVIE 
组中选择CATEGORY，MAX（SALES_AMT）
按类别
; 
  

解决方案

使用分析函数和子查询b
$ b

  select movie_id，movie_title，category，sales_amt 
 from（
 select movie_id，movie_title，category，sales_amt，
 row_number（）over（按类别order by sales_amt desc分区）r 
 from movie 
）其中r = 1 
  

I have a table MOVIE containing this data.

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

I need the Top movies data based on SALES_AMT with respect to CATEGORY

The required result is this:

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

If I use GROUP_BY option, I cannot select MOVIE_ID and MOVIE_TITLE

select CATEGORY, MAX(SALES_AMT)
from MOVIE
group by CATEGORY
;

解决方案

Try with analytic functions and subquery

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

这篇关于如何获取其他列不在GROUP BY子句中的oracle select sql？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！