Kết hợp bảng

Lập báo cáo trong SQL

Tyler Pernes

Learning & Development Consultant

Báo cáo mục tiêu

Huy chương vàng theo nhóm nhân khẩu
(Chỉ các nước Tây Âu)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------+--------------------+-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
| Winter   | Female Age 13-25   | 4     |
| Summer   | Female Age 26+     | 2     |
+----------+--------------------+-------+

Bảng liên quan

Tùy chọn A: JOIN trước, UNION sau

Bước 1: Thiết lập truy vấn trên với JOIN

SELECT 
    athlete_id, 
    gender, 
    age, 
    gold  
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id;

Query ran successfully!

Tùy chọn A: JOIN trước, UNION sau

Bước 2: Thiết lập truy vấn dưới + UNION hai

SELECT 
    athlete_id, 
    gender, 
    age, 
    gold  
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id
UNION ALL
SELECT 
    athlete_id, 
    gender, 
    age, 
    gold  
FROM winter_games AS wg
JOIN athletes AS a 
ON wg.athlete_id = a.id;

Tùy chọn B: UNION trước, JOIN sau

Bước 1: Tạo UNION ban đầu

SELECT 
    athlete_id, 
    gold  
FROM summer_games AS sg
UNION
SELECT 
    athlete_id, 
    gold  
FROM winter_games AS wg;

Tùy chọn B: UNION trước, JOIN sau

Bước 2: Đổi thành truy vấn con + JOIN

SELECT 
    athlete_id, 
    gender, 
    age, 
    gold  
FROM
    (SELECT 
         athlete_id, 
         gold  
    FROM summer_games AS sg
    UNION ALL
    SELECT athlete_id, gold  
    FROM winter_games AS wg) AS g
JOIN athletes AS a 
ON g.athlete_id = a.id;

So sánh

Tùy chọn A

SELECT 
    athlete_id, 
    gender, 
    age, 
    gold  
FROM summer_games AS sg
JOIN athletes AS a 
ON sg.athlete_id = a.id
UNION ALL
SELECT 
    athlete_id, 
    gender, 
    age, 
    gold  
FROM winter_games AS wg
JOIN athletes AS a 
ON wg.athlete_id = a.id;

Tùy chọn B

SELECT 
    athlete_id, 
    gender, 
    age, 
    gold  
FROM
    (SELECT 
         athlete_id, 
         gold  
    FROM summer_games AS sg
    UNION ALL
    SELECT athlete_id, gold  
    FROM winter_games AS wg) AS g
JOIN athletes AS a 
ON g.athlete_id = a.id;

Điểm chính

Nhiều cách để tạo cùng một báo cáo
Làm từng bước = dễ gỡ lỗi hơn

Đến giờ truy vấn!

Lập báo cáo trong SQL