如何将用户月度关联数据按月份列展示且每月允许多行？

问题背景 #

我需要得到特定格式的SQL查询结果，但当前尝试的两种写法要么报错，要么返回非预期结果。

尝试的SQL语句及问题 #

语句1（执行报错） #

SELECT u.Id 
      ,(SELECT c.Content FROM Contents c WHERE c.Month = '01' AND u.Id = c.Id) 'Month_1_Content'
      ,(SELECT c.Content FROM Contents c WHERE c.Month = '02' AND u.Id = c.Id) 'Month_2_Content'
      ,(SELECT c.Content FROM Contents c WHERE c.Month = '03' AND u.Id = c.Id) 'Month_3_Content'     
  FROM Users u;

报错信息：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

语句2（结果不符合预期） #

SELECT u.Id 
      ,c.Content 'Month_1_Content'
      ,d.Content 'Month_2_Content'
      ,e.Content 'Month_3_Content'
  FROM Users u
  LEFT OUTER JOIN Content c 
    ON c.Month = '01' AND u.Id= c.Id
  LEFT OUTER JOIN Content d 
    ON d.Month = '02' AND u.Id= d.Id
  LEFT OUTER JOIN Content e 
    ON e.Month = '03' AND u.Id= e.Id

期望查询结果 #

-------------------------------------------------------------
   Id   | Month_1_Content | Month_2_Content | Month_3_Content
-------------------------------------------------------------
 scott  |    01_scott_1   |    02_scott_1   |
 scott  |    01_scott_2   |                 |
 tiger  |                 |    02_tiger_1   |    03_tiger_1   
 tiger  |                 |    02_tiger_2   |    03_tiger_2  
 tiger  |                 |                 |    03_tiger_3   
 cat    |                 |                 | 
-------------------------------------------------------------

样本数据 #

Users表 #

Users
-----
 Id
-----
scott
tiger
cat
-----

Contents表 #

Contents
--------------------------------------
Month |   Id   | Sequence |  Content
--------------------------------------
01    | scott  |    1     | 01_scott_1
01    | scott  |    2     | 01_scott_2
02    | scott  |    1     | 02_scott_1
02    | tiger  |    1     | 02_tiger_1
02    | tiger  |    2     | 02_tiger_2
03    | tiger  |    1     | 03_tiger_1
03    | tiger  |    2     | 03_tiger_2
03    | tiger  |    3     | 03_tiger_3
--------------------------------------

解决方案 #

原查询的核心问题是：同一用户同一月份存在多条内容时，子查询会返回多行导致报错，多表连接则会产生笛卡尔积，无法匹配期望的行结构。以下是两种可行的写法：

写法1：基于序号关联各月内容 #

SELECT 
    u.Id,
    c1.Content AS Month_1_Content,
    c2.Content AS Month_2_Content,
    c3.Content AS Month_3_Content
FROM Users u
LEFT JOIN (
    SELECT Id, Sequence, Content FROM Contents WHERE Month = '01'
) c1 ON u.Id = c1.Id
FULL JOIN (
    SELECT Id, Sequence, Content FROM Contents WHERE Month = '02'
) c2 ON u.Id = c2.Id AND (c1.Sequence = c2.Sequence OR c1.Sequence IS NULL OR c2.Sequence IS NULL)
FULL JOIN (
    SELECT Id, Sequence, Content FROM Contents WHERE Month = '03'
) c3 ON u.Id = c3.Id AND (
    (c1.Sequence = c3.Sequence OR c1.Sequence IS NULL OR c3.Sequence IS NULL)
    OR (c2.Sequence = c3.Sequence OR c2.Sequence IS NULL OR c3.Sequence IS NULL)
)
WHERE 
    c1.Id IS NOT NULL OR c2.Id IS NOT NULL OR c3.Id IS NOT NULL OR u.Id = 'cat'
ORDER BY u.Id, 
    COALESCE(c1.Sequence, c2.Sequence, c3.Sequence);

写法2：通过CTE生成序号后匹配内容 #

WITH AllSequences AS (
    SELECT DISTINCT Sequence FROM Contents
    UNION
    SELECT 1 AS Sequence WHERE NOT EXISTS (SELECT 1 FROM Contents)
)
SELECT 
    u.Id,
    (SELECT Content FROM Contents WHERE Month='01' AND Id=u.Id AND Sequence=s.Sequence) AS Month_1_Content,
    (SELECT Content FROM Contents WHERE Month='02' AND Id=u.Id AND Sequence=s.Sequence) AS Month_2_Content,
    (SELECT Content FROM Contents WHERE Month='03' AND Id=u.Id AND Sequence=s.Sequence) AS Month_3_Content
FROM Users u
LEFT JOIN AllSequences s ON EXISTS (
    SELECT 1 FROM Contents WHERE Id=u.Id AND Sequence=s.Sequence
)
OR u.Id='cat'
ORDER BY u.Id, s.Sequence;

说明 #

• 写法1通过FULL JOIN关联各月内容，基于Sequence字段匹配行，确保同一序号的内容展示在同一行，无内容的位置留空。
• 写法2先提取所有存在的序号，再对每个用户+序号的组合查询各月内容，能精准生成期望的行结构，同时保留无内容用户的空行。

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