如何在SQL Server中关联3张表以生成指定格式的结果

问题描述 #

在SQL Server中，现有三张表：Students（学生表）、Hobbies（爱好表）、Colours（偏好颜色表）。每个学生拥有若干爱好和偏好颜色，需要生成一张整合表，展示每个学生的信息、对应的爱好及偏好颜色，要求爱好与颜色一一对应且无重复数据。

表结构及数据 #

Students表 #

StID Name
---------
101  Mike
102  Nancy
103  Tom
104  Lisa
105  John
106  Matt

Hobbies表 #

StID Hobby   PracticeDate
-------------------------
101  Bikes   12/2/2024
101  Music   24/2/2024
101  Movies  14/3/2024
102  Art     13/2/2024
102  Music   23/2/2024
103  Soccer  21/3/2024
103  Drawing 04/4/2024
103  Movies  22/2/2024
105  Music   11/2/2024
105  Bikes   26/3/2024

Colours表 #

StID Colour   PaintingDate
--------------------------
101  Blue     30/5/2024
101  Green    01/4/2024
102  Yellow   20/4/2024
102  Black    18/3/2024
102  Green    29/2/2024
103  Black    26/3/2024
103  Yellow   11/2/2024
103  Red      30/3/2024
104  Blue     07/2/2024
104  Pink     24/2/2024

期望结果 #

StID   Name   Hobby   PracticeDate   Colour   PaintingDate
-----------------------------------------------------
101  Mike     Bikes   12/2/2024      Blue     30/5/2024
101  Mike     Music   24/2/2024      Green    01/4/2024
101  Mike     Movies  14/3/2024      
102  Nancy    Art     13/2/2024      Yellow   20/4/2024
102  Nancy    Music   23/2/2024      Black    18/3/2024
102  Nancy                           Green    29/2/2024
103  Tom      Soccer  21/3/2024      Black    26/3/2024
103  Tom      Drawing 04/4/2024      Yellow   11/2/2024
103  Tom      Movies  22/2/2024      Red      30/3/2024
104  Lisa                            Blue     07/2/2024
104  Lisa                            Pink     24/2/2024
105  John     Music   11/2/2024     
105  John     Bikes   26/3/2024     
106  Matt                           
                                    

尝试的错误查询 #

直接使用左连接会产生笛卡尔积，导致重复数据，无法满足需求：

SELECT 
    p.StID,
    p.Name,
    ph.Hobby,
    ph.PracticeDate,
    lr.Colour,
    lr.PaintingDate
FROM 
    Students p
LEFT JOIN 
    Hobbies ph ON p.StID = ph.StID
LEFT JOIN 
    Colours lr ON p.StID = lr.StID

解决方案 #

通过给每个学生的爱好和颜色添加分组行号，再基于行号进行关联，就能实现一一对应且无重复的结果：

WITH HobbiesWithRow AS (
    -- 为每个学生的爱好按练习日期排序，生成行号
    SELECT 
        StID,
        Hobby,
        PracticeDate,
        ROW_NUMBER() OVER (PARTITION BY StID ORDER BY PracticeDate) AS RowNum
    FROM Hobbies
),
ColoursWithRow AS (
    -- 为每个学生的偏好颜色按绘画日期排序，生成行号
    SELECT 
        StID,
        Colour,
        PaintingDate,
        ROW_NUMBER() OVER (PARTITION BY StID ORDER BY PaintingDate) AS RowNum
    FROM Colours
)
-- 关联学生表、带行号的爱好表和颜色表
SELECT 
    s.StID,
    s.Name,
    h.Hobby,
    h.PracticeDate,
    c.Colour,
    c.PaintingDate
FROM Students s
-- 左连接爱好表，确保所有学生的爱好都被保留
LEFT JOIN HobbiesWithRow h ON s.StID = h.StID
-- 全连接颜色表，同时保留爱好或颜色中数量较多的部分
FULL JOIN ColoursWithRow c 
    ON s.StID = c.StID 
    AND ISNULL(h.RowNum, 0) = ISNULL(c.RowNum, 0)
-- 按学生ID和行号排序，保证结果顺序与期望一致
ORDER BY s.StID, ISNULL(h.RowNum, c.RowNum);

代码说明 #

1. CTE生成行号：使用ROW_NUMBER()函数，按StID分组，分别给爱好和颜色按日期排序生成行号，确保每个学生的爱好/颜色有唯一的序号。
2. 关联逻辑：通过StID和行号RowNum关联，FULL JOIN保证当爱好数量与颜色数量不一致时，多出来的行也能显示（对应字段为NULL）。
3. 排序：最终按学生ID和行号排序，让结果与期望格式一致。

建表及插入数据代码 #

-- Create Students table
CREATE TABLE Students (
    StID INT PRIMARY KEY,
    Name VARCHAR(50)
);

-- Insert data into Students table
INSERT INTO Students (StID, Name) VALUES
(101, 'Mike'),
(102, 'Nancy'),
(103, 'Tom'),
(104, 'Lisa'),
(105, 'John'),
(106, 'Matt');

-- Create Hobbies table
CREATE TABLE Hobbies (
    StID INT,
    Hobby VARCHAR(50),
    PracticeDate DATE,
    FOREIGN KEY (StID) REFERENCES Students(StID)
);

-- Insert data into Hobbies table
INSERT INTO Hobbies (StID, Hobby, PracticeDate) VALUES
(101, 'Bikes', '2024-02-12'),
(101, 'Music', '2024-02-24'),
(101, 'Movies', '2024-03-14'),
(102, 'Art', '2024-02-13'),
(102, 'Music', '2024-02-23'),
(103, 'Soccer', '2024-03-21'),
(103, 'Drawing', '2024-04-04'),
(103, 'Movies', '2024-02-22'),
(105, 'Music', '2024-02-11'),
(105, 'Bikes', '2024-03-26');

-- Create Colours table
CREATE TABLE Colours (
    StID INT,
    Colour VARCHAR(50),
    PaintingDate DATE,
    FOREIGN KEY (StID) REFERENCES Students(StID)
);

-- Insert data into Colours table
INSERT INTO Colours (StID, Colour, PaintingDate) VALUES
(101, 'Blue', '2024-05-30'),
(101, 'Green', '2024-04-01'),
(102, 'Yellow', '2024-04-20'),
(102, 'Black', '2024-03-18'),
(102, 'Green', '2024-02-29'),
(103, 'Black', '2024-03-26'),
(103, 'Yellow', '2024-02-11'),
(103, 'Red', '2024-03-30'),
(104, 'Blue', '2024-02-07'),
(104, 'Pink', '2024-02-24');

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