アーティストの違う複数バージョンがある曲 SELECT * FROM list WHERE tagTitle IN (SELECT tagTitle FROM list GROUP BY tagTitle HAVING Count(DISTINCT tagArtist)>=2 AND tagTitle!='') group by tagTitle,tagArtist;
5つ星の付いた曲が含まれるアルバムの曲を全て SELECT * from list where tagAlbum IN (SELECT tagAlbum from list where rating=50) ORDER BY tagTracknumber;
アルバムの評価の平均が星3つ以上のアルバムの曲全て SELECT * from list where tagAlbum IN (SELECT tagAlbum from (SELECT tagAlbum ,avg(rating) as avg_r from list group by tagAlbum) where avg_r>30) ORDER BY tagTracknumber;
平均以上の評価の曲全て SELECT * from list where rating>(SELECT avg(rating) from list);
まだ再生していない曲全て SELECT * FROM list WHERE lastplayed=0;
最終再生日時の平均が古いアルバム3つ SELECT * FROM list WHERE tagAlbum IN (SELECT tagAlbum FROM list GROUP BY tagAlbum ORDER BY Avg(lastplayed) LIMIT 3);
各アーティストごとの最終再生日時の一番古い曲 SELECT * FROM list WHERE lastplayed IN (SELECT Min(lastplayed) FROM list GROUP BY tagArtist) GROUP BY tagArtist;
