从单一行中选择重复项?
问题描述:
在这里,我有一个表叫CoreTracks:从单一行中选择重复项?
+---------+-----------------+----------+----------+----------+---------+
| TrackId | URI | ArtistID | Title | FileSize | BitRate |
+---------+-----------------+----------+----------+----------+---------+
| 1 | /home/music/... | 234 | atune | 8958223 | 192 |
| 2 | /home/music/... | 427 | goodsong | 6954373 | 192 |
| 3 | /home/music/... | 427 | goodsong | 4695698 | 128 |
| 4 | /home/music/... | 427 | goodsong | 5839962 | 160 |
| 5 | /home/music/... | 427 | goodsong | 4695698 | 128 |
| 6 | /home/music/... | 522 | another | 3458859 | 128 |
+---------+-----------------+----------+----------+----------+---------+
我想要检索的是:
+---------+-----------------+----------+----------+----------+---------+
| TrackId | URI | ArtistID | Title | FileSize | BitRate |
+---------+-----------------+----------+----------+----------+---------+
| 3 | /home/music/... | 427 | goodsong | 4695698 | 128 |
| 4 | /home/music/... | 427 | goodsong | 5839962 | 160 |
| 5 | /home/music/... | 427 | goodsong | 4695698 | 128 |
+---------+-----------------+----------+----------+----------+---------+
我试图消除基于具有相同的标题,同一艺术家ID重复,和不同的曲目ID,而不返回具有最高比特率和最高文件大小的条目。
我到目前为止是这样的:
SELECT * FROM CoreTracks
WHERE Title = Title AND ArtistID = ArtistID
AND BitRate != (SELECT MAX(BitRate) FROM CoreTracks WHERE Title = Title AND ArtistID = ArtistID)
AND FileSize != (SELECT MAX(FileSize) FROM CoreTracks WHERE Title = Title AND ArtistID = ArtistID);
它返回每个轨道。我错过了什么使这个查询工作?
答
这会得到相反的(即跳过式两份):
SELECT c1.*
FROM CoreTracks c1
,(SELECT Title, ArtistID, MAX(FileSize) AS maxFileSize, MAX(BitRate) maxBitRate
FROM CoreTracks
GROUP BY Title, ArtistID) c2
WHERE c1.Title = c2.Title
AND c1.ArtistID = c2.ArtistID
AND (c1.FileSize = c2.maxFileSize OR c1.BitRate = c2.maxBitRate)
而重复:
SELECT c1.*
FROM CoreTracks c1
,(SELECT Title, ArtistID, MAX(FileSize) AS maxFileSize, MAX(BitRate) maxBitRate
FROM CoreTracks
GROUP BY Title, ArtistID) c2
WHERE c1.Title = c2.Title
AND c1.ArtistID = c2.ArtistID
AND (c1.FileSize != c2.maxFileSize AND c1.BitRate != c2.maxBitRate)
答
从标题中选择max(trackId),ArtistID按BitRate asc排序 - 然后在由trackId选择的另一个选项中进行换行?
答
SELECT A.* FROM CoreTracks A, CoreTracks B
WHERE A.Title = B.Title AND A.ArtistID = B.ArtistID AND A.trackId != B.trackId
HAVING A.BitRate != MAX(A.BitRate) AND A.FileSize != MAX(A.FileSize);
尚未测试,但应该工作。
答
尝试自连接,而不是正常的查询。 我没试过。
真不可思议。非常感谢! – 2012-03-27 22:28:07
而不是选择重复的结果,我该如何去删除它们? – 2012-04-05 04:05:36
@BrandonMinton你可以做一个“从CoreTracks WHERE TrackId中删除(SELECT c1.TrackId FROM ...) – Glenn 2012-04-05 11:32:21