SQLZOO技术求助：如何按字母顺序筛选各洲首个国家

Hey there! Let's walk through this problem step by step to clear up the confusion—first why your initial query didn't work, then exactly how the correct solution functions, especially that tricky <= ALL part.

Why Your First Attempt Didn't Deliver #

Your original query:

SELECT DISTINCT continent, name FROM world WHERE name LIKE 'A%' ORDER BY name

has two critical flaws:

• You're only filtering countries that start with "A", but the task asks for the alphabetically first country per continent—which might not start with A (even though many do, it's not a requirement).
• DISTINCT can't fix duplicate continents here because you're still returning every "A-starting" country in a continent, not just the very first one in alphabetical order. For example, Europe has multiple countries starting with A, so you'd see Europe listed multiple times.

Breaking Down the Correct Query #

Let's unpack the working solution line by line:

SELECT continent, name FROM world x WHERE name <= ALL(SELECT name FROM world y WHERE x.continent = y.continent)

This uses a correlated subquery—meaning the subquery references a value from the main query. Here's the play-by-play:

1. Table Aliases: x is an alias for the main world table (think of it as "the current country we're checking"), and y is an alias for the same table in the subquery ("every other country in the same continent as x").

2. The Subquery: SELECT name FROM world y WHERE x.continent = y.continent pulls the name of every country that shares the same continent as the current country x. For example, if x is Albania (Europe), this subquery returns all European country names.

3. The <= ALL Logic: This is the core of the solution. name <= ALL(...) translates to: "The name of the current country x is less than or equal to every single name returned by the subquery".

   When is this condition true? Only when x.name is the alphabetically first (smallest) name in its continent. If there was even one country in the continent with a name that comes before x.name, then x.name wouldn't be less than or equal to that country's name—so the condition would fail.

   For example:

   • If x is Albania, every other European country's name (like Austria, Belgium, etc.) comes after it alphabetically. So Albania <= ALL(European names) is true, so Albania is included in the results.
   • If x is Austria, Albania's name comes before it, so Austria <= Albania is false. The <= ALL condition fails, so Austria is excluded.

To Sum It Up #

This query checks every country individually, and only keeps it if it's the first (alphabetically) in its continent. That's how you end up with exactly one country per continent—perfectly matching the task's requirements.

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