如何编写SQL查询找出成员均来自不同国家的乐队？

Got it, let's work through how to identify bands where every member comes from a distinct country. The key logic here is to compare two values for each band: the total number of members, and the number of unique countries those members represent. If these two numbers match, that means no two members share the same country.

Solution 1: Include Members with Unknown Countries (Matching Your Original Query Structure) #

This version uses a left join just like your original SQL, and handles cases where some members might have no recorded country (NULL). Keep in mind that if multiple members have a NULL country, they'll be treated as the same "unknown" group—so such bands will only pass the filter if there's only one member with an unknown country.

SELECT b.BandID
FROM bandmusicians b
LEFT JOIN Musicians m ON b.MusicianID = m.MusicianID
GROUP BY b.BandID
HAVING COUNT(DISTINCT m.MusicianCountry) = COUNT(b.MusicianID)

Breakdown of the logic: #

• Group by BandID: We cluster all records by band so we can analyze each group individually.
• Count unique countries: COUNT(DISTINCT m.MusicianCountry) tallies how many distinct countries are present in the band's member list.
• Count total members: COUNT(b.MusicianID) counts the full number of members in the band (since every entry in bandmusicians maps to one member, this won't have NULL values for the member ID).
• HAVING filter: This clause checks if the two counts are equal—proving every member's country is unique.

Solution 2: Exclude Members with Unknown Countries #

If you want to only consider bands where every member has a confirmed country (and those countries are all unique), use an inner join and add a check for non-null country values:

SELECT b.BandID
FROM bandmusicians b
JOIN Musicians m ON b.MusicianID = m.MusicianID
WHERE m.MusicianCountry IS NOT NULL
GROUP BY b.BandID
HAVING COUNT(DISTINCT m.MusicianCountry) = COUNT(b.MusicianID)

This ensures we only look at members with a recorded country, and verifies that all those countries are distinct for the band.

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