mysql - 如何查找重复记录，而不使用聚合函数和分组依据？

Question

db中有歌手和歌曲表，我们必须通过不使用聚合函数和按关键字分组来找到歌曲数量最多的人。

土耳其语表

表已翻译

song(sarkino, name, tour, duration, composerno, author no)
singer(singer, name, type, birthDate, birthPlace)
album(albumno, name, year, price, singer, stock quantity)
Song in the album(albumno, song, order)
composer(composcino, name, tour)
author(author, name)

问题原文：
在此处输入图片描述

问题翻译：

求解写歌词最多的作者的作者编号，就像我们在课堂上（在演示文稿中的示例中）所做的那样，不使用聚合函数或分组依据。

标签： mysqlsqlpostgresqlgroup-byaggregate-functions

Answer 1

正如 Ergest 所提到的，非常标准的方法是使用GROUP BYwith COUNT。但是，今天你的老师喜欢挑战。

GarethD的替代解决方案（绝对符合要求）

我想在我的回答中回顾一下GarethD的评论，这一切都归功于他。

如果教师认为 COUNT(*) 是一个聚合函数，即使在窗口函数中使用，这也不是一个可行的解决方案。如果唯一的要求是让歌手拥有最多歌曲，那么另一种选择是使用 ROW_NUMBER() 几乎可以肯定这不是任何人定义的聚合函数

SELECT s.name, 
       ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY Songs.id) AS number_of_songs
FROM singers AS s
    JOIN Songs
        ON Songs.singer_id = s.id
ORDER BY number_of_songs DESC  
LIMIT 1

姓名	number_of_songs
林肯公园	5

GarethD的DBFiddle

我原来的解决方案使用COUNT OVER PARTITION BY

您将需要使用 的高级版本COUNT，通过与 结合，与OVER (PARTITION BY singer_id)相比具有相同的效果COUNT GROUP BY singer_id。这将计算指定分区上的所有歌曲记录singer_id，这意味着一个歌手 ID 的总歌曲记录。

由于您添加了一堆不同 SQL 数据库类型的标签，我将选择Postgres来编写示例演示片段。不要担心，因为COUNT(*) OVER PARTITION每个 SQL 数据库的语法可能都相同

WITH singers(id, name) AS (
    VALUES(1, 'Lady Gaga'),
    (2, 'Elton John'),
    (3, 'Linkin Park')
),
songs(id, name, singer_id)
AS (
    VALUES(1, 'Born This Way', 1),
    (2, 'Rocket Man', 2),
    (3, 'Numb', 3),
    (4, 'In The End', 3),
    (5, 'Papercut', 3),
    (6, 'Can You Feel The Love Tonight?', 2),
    (7, 'Tiny Dancer', 2),
    (8, 'Your Song', 2),
    (9, 'Waiting For The End', 3),
    (10, 'Forgotten', 3)
)

SELECT singers.*, song_count.number_of_songs
FROM singers
JOIN
(SELECT DISTINCT singer_id, COUNT(*) OVER (PARTITION BY singer_id) as number_of_songs
FROM songs) song_count
ON singers.id = song_count.singer_id
ORDER BY song_count.number_of_songs DESC;

ID	姓名	number_of_songs
3	林肯公园	5
2	埃尔顿约翰	4
1	Lady Gaga	1

DISTINCTinSELECT子句用于删除singer_id number_of_songsselect的重复记录

SELECT singer_id, COUNT(*) OVER (PARTITION BY singer_id) as number_of_songs
FROM songs;

歌手 ID	number_of_songs
1	1
2	4
2	4
2	4
2	4
3	5
3	5
3	5
3	5
3	5

在这里查看这个db fiddle

参考 Postgresql 窗口函数教程