SQL查询需求：筛选含重复值且属性有差异的行（MediaMonkey场景）

Hey there! Since you're new to SQL, let's break this down simply to get you the playlist of cover, adaptation, or live versions you need from MediaMonkey's SQLite database.

Core Goal #

We want to find groups of songs that share the same Musicbrainz Work ID (stored in Custom4), where at least one song in the group has an AcoustID (Custom5 is not empty) and at least one song does NOT have an AcoustID. This pattern indicates different versions of the same work (like covers or live takes) since the AcoustID would differ (or be missing) between distinct recordings.

Step 1: Identify Qualifying Work IDs #

First, we'll get a list of Work IDs that meet our "mixed AcoustID presence" condition:

SELECT Custom4
FROM Songs
WHERE Custom4 IS NOT NULL  -- Ignore entries without a valid Work ID
GROUP BY Custom4
HAVING 
  COUNT(CASE WHEN Custom5 IS NOT NULL THEN 1 END) > 0  -- At least one song has AcoustID
  AND COUNT(CASE WHEN Custom5 IS NULL THEN 1 END) > 0; -- At least one song doesn't

The HAVING clause uses conditional counting to verify both conditions are true for the group.

Step 2: Get All Songs in These Work Groups #

Next, we'll join this list back to the Songs table to fetch every song in these qualifying groups:

SELECT s.*
FROM Songs s
JOIN (
    -- Subquery from Step 1
    SELECT Custom4
    FROM Songs
    WHERE Custom4 IS NOT NULL
    GROUP BY Custom4
    HAVING 
      COUNT(CASE WHEN Custom5 IS NOT NULL THEN 1 END) > 0
      AND COUNT(CASE WHEN Custom5 IS NULL THEN 1 END) > 0
) AS qualifying_works ON s.Custom4 = qualifying_works.Custom4
ORDER BY s.Custom4, s.Custom5; -- Sort to group same works together for easy comparison

This gives you full details for every song in groups with mixed AcoustID status.

Optional: More Readable Output #

If you want a cleaner view to quickly spot versions, adjust the select to show key fields instead of all columns:

SELECT 
    s.Custom4 AS Musicbrainz_Work_ID,
    s.Title,
    s.ArtistName,
    CASE 
      WHEN s.Custom5 IS NOT NULL THEN 'Has AcoustID' 
      ELSE 'No AcoustID' 
    END AS AcoustID_Status
FROM Songs s
JOIN (
    SELECT Custom4
    FROM Songs
    WHERE Custom4 IS NOT NULL
    GROUP BY Custom4
    HAVING 
      COUNT(CASE WHEN Custom5 IS NOT NULL THEN 1 END) > 0
      AND COUNT(CASE WHEN Custom5 IS NULL THEN 1 END) > 0
) AS qualifying_works ON s.Custom4 = qualifying_works.Custom4
ORDER BY s.Custom4, AcoustID_Status;

Quick Tips for MediaMonkey #

• To turn this into a playlist: Go to Playlists > New Auto-Playlist, switch to the Advanced tab, and paste your SQL query into the field.
• Double-check that Custom4 and Custom5 are correctly mapped to your Musicbrainz Work ID and AcoustID (it sounds like you already confirmed this, but just a quick sanity check!).

内容的提问来源于stack exchange，提问作者Matt Henderson