我有一个这样的选择:
SELECT persistent_id, artist, name, play_count FROM tracks ORDER BY play_count DESC其中,结果是:
7FE074BC7C6429F3 artist1 track1 88
EC3CFAF957357763 artist2 track2 87
E40063C38F3010B2 artist3 track6 49
B09423DCE75A0908 artist2 track3 43
1F6E5B2E1FC1CC85 artist2 track4 15
2BB3103CABB9B4DC artist3 track5 6我如何重写这个查询,以便我可以指定行,以便我可以指定(使用persistent_id) B09423DCE75A0908 + 1F6E5B2E1FC1CC85以及E40063C38F3010B2 + 2BB3103CABB9B4DC应该求和?因此,结果将是:
7FE074BC7C6429F3 artist1 track1 88
EC3CFAF957357763 artist2 track2 87
B09423DCE75A0908 artist2 track3 58
E40063C38F3010B2 artist3 track6 55我试着这样做:
sum(DISTINCT CASE WHEN persistent_id = 'B09423DCE75A0908' THEN play_count END) AS plays1,
sum(DISTINCT CASE WHEN persistent_id = '1F6E5B2E1FC1CC85' THEN play_count END) AS plays2然后尝试将plays1和plays2合计为play_count,但是除了将这两个persistent_id的play_count放在同一行之外,我没有做更多的事情。
发布于 2021-01-29 04:03:00
看起来你需要一个关于persistent_id的案例
SELECT case when persistent_id IN ('B09423DCE75A0908' , '1F6E5B2E1FC1CC85')
THEN 'B09423DCE75A0908'
when persistent_id IN ( 'E40063C38F3010B2','2BB3103CABB9B4DC ')
THEN 'E40063C38F3010B2' ELSE persistent_id END persistent_id
min(artist), min(name), sum(play_count )
FROM tracks
GROUP BY persistent_id
ORDER BY play_count DESC对于最旧的mysql版本,您必须重复group by中的聚合键的代码
SELECT case when persistent_id IN ('B09423DCE75A0908' , '1F6E5B2E1FC1CC85')
THEN 'B09423DCE75A0908'
when persistent_id IN ( 'E40063C38F3010B2','2BB3103CABB9B4DC ')
THEN 'E40063C38F3010B2' ELSE persistent_id END persistent_id
min(artist), min(name), sum(play_count )
FROM tracks
GROUP BY ase when persistent_id IN ('B09423DCE75A0908' , '1F6E5B2E1FC1CC85') THEN 'B09423DCE75A0908'
when persistent_id IN ( 'E40063C38F3010B2','2BB3103CABB9B4DC ') THEN
THEN 'E40063C38F3010B2' ELSE persistent_id END
ORDER BY play_count DESC发布于 2021-01-29 04:10:17
SELECT
CASE WHEN persistent_id IN ('B09423DCE75A0908','1F6E5B2E1FC1CC85','E40063C38F3010B2')
THEN 'B09423DCE75A0908' else persistent_id END as persitent_id,
SUM(play_count)
FROM tracks
ORDER BY play_count DESC
GROUP BY CASE WHEN persistent_id IN ('B09423DCE75A0908','1F6E5B2E1FC1CC85','E40063C38F3010B2')
THEN 'B09423DCE75A0908' else persistent_id ENDhttps://stackoverflow.com/questions/65944337
复制相似问题