如何通过连接将表中一列关联至另一表多列？附两表新增列需求

Alright, let's tackle this problem. It looks like you need to join the Customer table with the Age table on CustomerID, plus add a column (I'll assume it's HasAge since the name was cut off) to flag whether each customer has a corresponding age entry. Here's a clean SQL Server solution tailored to your table variables:

Full SQL Implementation #

-- Define and populate the Customer table variable
declare @customer table (CustomerID INT, CustomerName VARCHAR(10))
INSERT INTO @customer VALUES 
    (1, 'Shane'), (2, 'Daniel'), (3, 'Karim'), 
    (4, 'Eric'), (5, 'Zoe'), (6, 'Jack')

-- Define and populate the Age table variable
declare @age table (CustomerID INT, Age INT)
INSERT INTO @age VALUES 
    (1, 20), (2, 19), (3, 12), (6, 30)

-- Join tables and add the HasAge flag column
SELECT 
    c.CustomerID,
    c.CustomerName,
    a.Age,
    -- Flag: 1 = has age record, 0 = no age record
    CASE 
        WHEN a.CustomerID IS NOT NULL THEN 1 
        ELSE 0 
    END AS HasAge
FROM @customer c
LEFT JOIN @age a 
    ON c.CustomerID = a.CustomerID

Key Details Explained #

• LEFT JOIN: This ensures we keep every customer from the Customer table, even if they don't have an entry in the Age table (like Eric and Zoe in your sample data). If you only wanted customers with existing age records, you'd use INNER JOIN instead.
• CASE Statement: This generates the HasAge column by checking if the joined Age table has a matching record. We use a.CustomerID IS NOT NULL as the check because LEFT JOIN returns NULL for columns from the Age table when there's no match.
• Alternative Simplified Syntax: If you're using SQL Server 2012 or later, you can use IIF() as a shorter replacement for the CASE statement:

  IIF(a.CustomerID IS NOT NULL, 1, 0) AS HasAge
  

  This does exactly the same thing, just with less code.

Sample Output #

Running the query will give you this result:

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