作者:gogo迷失的大G | 来源:互联网 | 2023-05-17 15:07
HereIhaveatablecalledCoreTracks:这里我有一个名为CoreTracks的表:+---------+-----------------+--------
Here I have a table called CoreTracks:
这里我有一个名为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 |
+---------+-----------------+----------+----------+----------+---------+
What I want retrieved is this:
我想要检索的是:
+---------+-----------------+----------+----------+----------+---------+
| 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 |
+---------+-----------------+----------+----------+----------+---------+
I am trying to remove duplicates based on having the same title, same artist id, and a different track id while not returning the entry with the highest bitrate and the highest filesize.
我试图删除重复项基于具有相同的标题,相同的艺术家ID和不同的轨道ID,而不返回具有最高比特率和最高文件大小的条目。
What I have so far is this:
到目前为止我所拥有的是:
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);
Which returns every track. What am I missing to make this query work?
返回每个曲目。我错过了什么使这个查询工作?
4 个解决方案