按ID筛选TOP事物组

Question

我有一个任务要过滤:Artist, song_count, most_popular_artistrongong, most_acoustic_artistrongong, most_energised_artistrongong

表

怎么做?需要在select中使用select吗?

我现在的代码:

SELECT artists AS artist, count(name) AS songs, name , acousticness, energy , popularity
FROM lt.artist
GROUP BY artists 
ORDER BY popularity DESC, acousticness DESC, energy DESC

结果

但是在声学和能量栏中，我需要得到最具声学性，能量的歌曲名称以及歌手演唱的地方，像这样:

artist|song_count|most_popular_song|most_acoustic_song |most_energised_song

Justin Timberlake | 150 | Mirrors | Cry me a river | Can’t stop the feeling 

Answer 1

这使得查询异常复杂。您的要求是从表中为每个艺术家提取四个不同的项目。

那么，从一个子查询开始获取度量。

SELECT artists,
COUNT(*) song_count, 
MAX(energy) energy,
MAX(popularity) popularity,
MAX(acousticness) acousticness
FROM artist
GROUP BY artists

那么您必须使用JOIN操作来恢复具有这些MAX值的歌曲名称。

SELECT m.artists artist
m.song_count,
pop.name most_popular,
aco.name most_acoustic,
ene.name most_energized
FROM (   /* the subquery from ^^^ */
SELECT artists,
COUNT(*) song_count, 
MAX(energy) energy,
MAX(popularity) popularity,
MAX(acousticness) acousticness
FROM artist
GROUP BY artists
) m
JOIN artist pop 
ON m.artists = pop.artists AND m.popularity = pop.popularity
JOIN artist aco
ON m.artists = aco.artists AND m.acousticness= aco.acousticness
JOIN artist ene
ON m.artists = ene.artists AND m.energy= ene.energy
ORDER BY m.popularity DESC, m.acousticness DESC, m.energy DESC

SQL缺乏"检索具有最高值的行"的简单语法。不幸的是，在GROUP BY操作中。所以你必须这样做。

好消息呢?给定正确的索引，查询优化器可以很好地处理这类事情。合适的索引可能是(不保证)

ALTER TABLE artist 
ADD INDEX (artist, energy, popularity, acousticness);