如何为Shop与Info表的连接结果分配别名并完成指定查询

Hey there! Great question—yes, you absolutely can assign a name (called a table alias) to your joined result set to make your query cleaner and easier to work with. Let's break this down step by step.

First: Aliasing the Joined Tables #

When joining tables, you can alias either individual tables or the entire joined subquery. For your existing join, rewriting it with table aliases is the standard, more readable approach:

SELECT s.Name, s.Country, i.Category
FROM Shop s
JOIN Info i ON s.Name = i.Name;

Here, s acts as an alias for the Shop table and i for the Info table—this cuts down on typing and makes the query logic clearer.

If you want to treat the entire joined result as a named, reusable set, you can use a Common Table Expression (CTE). This lets you reference the joined data like a regular table in subsequent parts of your query:

WITH JoinedShops AS (
    SELECT s.Name, s.Country, i.Category
    FROM Shop s
    JOIN Info i ON s.Name = i.Name
)
-- Now you can query from JoinedShops just like any other table
SELECT * FROM JoinedShops;

Second: Implementing Your Required Filter #

To get records where all shops in a category share the same country, we first identify which categories meet that condition, then pull the corresponding records. Here are two reliable approaches:

Approach 1: Using CTEs with Aggregation #

We'll first filter categories where the number of distinct countries is exactly 1, then join back to our joined dataset:

WITH JoinedShops AS (
    SELECT s.Name, s.Country, i.Category
    FROM Shop s
    JOIN Info i ON s.Name = i.Name
),
ValidCategories AS (
    SELECT Category
    FROM JoinedShops
    GROUP BY Category
    HAVING COUNT(DISTINCT Country) = 1
)
SELECT js.*
FROM JoinedShops js
JOIN ValidCategories vc ON js.Category = vc.Category;

Approach 2: Using a Correlated Subquery #

If you prefer a single-query solution without CTEs, you can use a subquery in the WHERE clause to validate each category:

SELECT s.Name, s.Country, i.Category
FROM Shop s
JOIN Info i ON s.Name = i.Name
WHERE (
    SELECT COUNT(DISTINCT Country)
    FROM Shop s2
    JOIN Info i2 ON s2.Name = i2.Name
    WHERE i2.Category = i.Category
) = 1;

Both approaches will return your expected output:

Key Notes #

• Table aliases (s, i, JoinedShops) make queries shorter and easier to maintain.
• The HAVING COUNT(DISTINCT Country) = 1 condition is what filters categories to only those where all shops share the same country.

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