使用GROUP BY具有多列和聚合函数的SQL查询

2024-05-02 • 问答

我在使用SQL查询时遇到麻烦。我有以下movie表：

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID_MOVIE                                  NOT NULL NUMber(4)
 TITLE                                     NOT NULL VARCHAR2(30)
 GENRE                                     NOT NULL VARCHAR2(30)
 YEAR                                      NOT NULL NUMber(4)
 COUNTRY                                   NOT NULL VARCHAR2(30)
 DURATION                                  NOT NULL NUMber(3)
 BUDGET                                             NUMber(10)
 INCOMES                                            NUMber(10)
 ID_MOVIE_PREV                                      NUMber(4)
 ID_DIRECTOR

我尝试了以下命令：select m.genre,max(m.budget),m.title from movie m group by m.genre,m.title;并得到了以下结果：

GENRE                          MAX(M.BUDGET) TITLE
------------------------------ ------------- ------------------------------
Western                              1200000 The Good,the Bad and the Ugly
Horror                                806947 Psycho
Crime                                7000000 The Godfather: Part III
action                             185000000 The Dark Knight
Drama                               26000000 Philadelphia
Drama                               13000000 In the Name of the Father
action                             150000000 Batman Begins
Historical                          23800000 The Last Emperor
Science-fiction                      5800000 Planet of the Apes
Crime                                7000000 The Godfather
action                             230000000 The Dark Knight Rises

GENRE                          MAX(M.BUDGET) TITLE
------------------------------ ------------- ------------------------------
Comedy                              28000000 Zoolander
Crime                                9000000 Pulp Fiction
Crime                               13000000 The Godfather: Part II
War                                 70000000 Saving Private Ryan
Science-fiction                     28000000 Blader Runner
Drama                               33000000 Gran Torino

我想获得每个性别的最大值的标题。谁能告诉我我的错误在哪里？预先谢谢你！

我怀疑您的查询实际上并未聚合，因为您希望(genre,title)元组在表中是唯一的。

您可以使用相关子查询来过滤表：

select genre,title,budget
from movie m
where budget = (
    select max(m1.budget)
    from movie m1
    where m1.genre = m.genre
)

在我看来，这似乎是表达您想要达到的目标的最简单方法。为了提高性能，请考虑在(genre,budget)上使用索引。

这是 demo on DB Fiddle （基于以上假设，您可以将当前结果集视为原始数据）：

GENRE           | TITLE                          |    BUDGET
:-------------- | :----------------------------- | --------:
Western         | The Good,the Bad and the Ugly |   1200000
Horror          | Psycho                         |    806947
Historical      | The Last Emperor               |  23800000
Action          | The Dark Knight Rises          | 230000000
Comedy          | Zoolander                      |  28000000
Crime           | The Godfather: Part II         |  13000000
War             | Saving Private Ryan            |  70000000
Science-fiction | Blader Runner                  |  28000000
Drama           | Gran Torino                    |  33000000

您可以使用row_number()分析函数：

select genre,budget,title
  from
  (
      select genre,row_number() over (partition by genre order by budget desc) as rn       
        from movie 
   )
  where rn = 1

分组是按分区进行的，预算的最大值可通过按降序排序来找到。

Demo

在Oracle中，您可以使用聚合：

select genre,max(title) keep (dense_rank first order by budget desc) as title,max(budget) as budget
from movie m
group by genre;

keep表达式本质上在执行获取第一个值的功能。

使用GROUP BY具有多列和聚合函数的SQL查询

xukaizhan 回答：使用GROUP BY具有多列和聚合函数的SQL查询

大家都在问