问题

我提供了创建两个示例表格以回答我的问题的语句。

在这个示例中，第二个表格由于具有多个classID而多次包含相同的student id。然而，我需要查询结果在同一行上以水平方式显示。(每个studentid的最大classID数量为15)

您将如何编写一个选择语句，将两个表格连接起来，所有classID数据在同一列中重复，就像下面的示例一样：

```plaintext
创建表 Student (
 学生ID int,
 名字 varchar (255),
 姓 varchar (255)
);

创建表 Classes (
 学生ID int,
 课程ID int,
 课程名 varchar (255),
 课程费用 int
);

插入到 Student (学生ID, 名字, 姓)
值 
(123, 'Carol', 'Dwek'),
(456, 'Cal', 'Newport');

插入到 Classes (学生ID, 课程ID, 课程名,课程费用)
值 
(123, 972, '心理学',30),
(456, 214, '专注',99),
(123, 903, '社会学',30),
(456, 851, '冥想',99),
(456, 911, '阅读',20),
(456, 111, '深度工作',50),
(456, 117, '时间管理',25),
(456, 999, '目标设定',50);

结果:

<details>
<summary>英文:</summary>

I&#39;ve provided statements to create the two example tables for my question below.

In this example, the second table contains the same student id multiple times due to having multiple classIDs. However, I need the query results to be displayed horizontally on one row. (The max # of classID&#39;s per studentid is 15)

How would you write a select statement that joins the two tables and all classID data repeats within the same column like the example below:

CREATE TABLE Student (
StudentId int,
FirstName VarChar (255),
LastName VarChar (255)
);

CREATE TABLE Classes (
StudentId int,
ClassId int,
ClassName VarChar (255),
ClassCost int
);

INSERT INTO Student (StudentId, FirstName, LastName)
VALUES
(123, 'Carol', 'Dwek'),
(456, 'Cal', 'Newport');

INSERT INTO Classes (StudentId, ClassId, ClassName,ClassCost)
VALUES
(123, 972, 'Psychology',30),
(456, 214, 'Focus',99),
(123, 903, 'Sociology',30),
(456, 851, 'Meditation',99),
(456, 911, 'Reading',20),
(456, 111, 'Deep Work',50),
(456, 117, 'Time Management',25),
(456, 999, 'Goal Setting',50);

Results: 
[![enter image description here][1]][1]


  [1]: https://i.stack.imgur.com/SOvBm.png

</details>


# 答案1
**得分**: 2

只有在事先知道学生可能有多少潜在课程时，才能在单个查询中实现这一点。如果你不知道或无法猜测一个合理的最大值，SQL语言简单地无法在单个语句中产生所需的输出。如果你能猜测最大的课程负载，查询如下：

```sql
WITH NumberedClasses As (
   SELECT *, row_number() over (partition by StudentID order by ClassID) rn
   FROM Classes
)
SELECT s.*
    ,c1.ClassId, c1.ClassName, c1.ClassCost
    ,c2.ClassID, c2.ClassName, c2.ClassCost
    -- ...
    ,cn.ClassID, cn.ClassName, cn.ClassCost
FROM Student s
LEFT JOIN NumberedClasses c1 ON c1.StudentID = s.StudentID AND c1.rn = 1
LEFT JOIN NumberedClasses c2 ON c2.StudentID = s.StudentID And c2.rn = 2
-- ...
LEFT JOIN NumberedClasses cn ON cn.StudentID = s.StudentID And cn.rn = {n}

注意：这往往很慢，而且不仅仅是慢一点；如果你有合理数量的数据，可能需要几分钟才能完成（或更长时间）。是的，你确实必须在两个地方重复自己，就像你有可能的课程注册一样。

这里还值得一提的是，这种PIVOT方法与支持所有关系数据库的形式化集合理论相抵触。因此，通常最好在客户端代码或报告工具中完成这项工作。

WITH NumberedClasses As (
   SELECT *, row_number() over (partition by StudentID order by ClassID) rn
   FROM Classes
)
SELECT s.*
    ,c1.ClassId, c1.ClassName, c1.ClassCost
    ,c2.ClassID, c2.ClassName, c2.ClassCost
    -- ...
    ,cn.ClassID, cn.ClassName, cn.ClassCost
FROM Student s
LEFT JOIN NumberedClasses c1 ON c1.StudentID = s.StudentID AND c1.rn = 1
LEFT JOIN NumberedClasses c2 ON c2.StudentID = s.StudentID And c2.rn = 2
-- ...
LEFT JOIN NumberedClasses cn ON cn.StudentID = s.StudentID And cn.rn = {n}

答案2

得分: 2

如果您愿意在学生可以参加的课程数量上使用硬编码的上限，那么一种比多次自连接效果更好的方法（多次自连接可能会多次重新评估行编号）是使用编号进行数据透视。

提供行编号的相同排序（StudentId，ClassId）也可用于按StudentId进行分组（由下面计划中的primary key (StudentId, ClassId)提供）。

查询仍然相当丑陋，最好在应用程序中完成（如果有应用程序并且您不只是在SSMS中运行adhoc查询以查看结果）。

With Numbered As
(
SELECT *,
       rn = row_number() over (PARTITION BY StudentID ORDER BY ClassID) 
FROM Classes
), Pivoted As
(
SELECT StudentId, 
       ClassId1 = MAX(CASE WHEN rn = 1 THEN ClassId END), 
       ClassName1 = MAX(CASE WHEN rn = 1 THEN ClassName END), 
       ClassCost1 = MAX(CASE WHEN rn = 1 THEN ClassCost END),
       ClassId2 = MAX(CASE WHEN rn = 2 THEN ClassId END), 
       ClassName2 = MAX(CASE WHEN rn = 2 THEN ClassName END), 
       ClassCost2 = MAX(CASE WHEN rn = 2 THEN ClassCost END),
       ClassId3 = MAX(CASE WHEN rn = 3 THEN ClassId END), 
       ClassName3 = MAX(CASE WHEN rn = 3 THEN ClassName END), 
       ClassCost3 = MAX(CASE WHEN rn = 3 THEN ClassCost END),
       ClassId4 = MAX(CASE WHEN rn = 4 THEN ClassId END), 
       ClassName4 = MAX(CASE WHEN rn = 4 THEN ClassName END), 
       ClassCost4 = MAX(CASE WHEN rn = 4 THEN ClassCost END),
       ClassId5 = MAX(CASE WHEN rn = 5 THEN ClassId END), 
       ClassName5 = MAX(CASE WHEN rn = 5 THEN ClassName END), 
       ClassCost5 = MAX(CASE WHEN rn = 5 THEN ClassCost END),
       ClassId6 = MAX(CASE WHEN rn = 6 THEN ClassId END), 
       ClassName6 = MAX(CASE WHEN rn = 6 THEN ClassName END), 
       ClassCost6 = MAX(CASE WHEN rn = 6 THEN ClassCost END)
FROM Numbered
GROUP BY StudentId
)
SELECT S.FirstName, S.LastName, P.*
FROM   Student S
       JOIN Pivoted P
         ON P.StudentId = S.StudentId

With Numbered As
(
SELECT *,
rn = row_number() over (PARTITION BY StudentID ORDER BY ClassID) 
FROM Classes
), Pivoted As
(
SELECT StudentId, 
ClassId1 = MAX(CASE WHEN rn = 1 THEN ClassId END), 
ClassName1 = MAX(CASE WHEN rn = 1 THEN ClassName END), 
ClassCost1 = MAX(CASE WHEN rn = 1 THEN ClassCost END),
ClassId2 = MAX(CASE WHEN rn = 2 THEN ClassId END), 
ClassName2 = MAX(CASE WHEN rn = 2 THEN ClassName END), 
ClassCost2 = MAX(CASE WHEN rn = 2 THEN ClassCost END),
ClassId3 = MAX(CASE WHEN rn = 3 THEN ClassId END), 
ClassName3 = MAX(CASE WHEN rn = 3 THEN ClassName END), 
ClassCost3 = MAX(CASE WHEN rn = 3 THEN ClassCost END),
ClassId4 = MAX(CASE WHEN rn = 4 THEN ClassId END), 
ClassName4 = MAX(CASE WHEN rn = 4 THEN ClassName END), 
ClassCost4 = MAX(CASE WHEN rn = 4 THEN ClassCost END),
ClassId5 = MAX(CASE WHEN rn = 5 THEN ClassId END), 
ClassName5 = MAX(CASE WHEN rn = 5 THEN ClassName END), 
ClassCost5 = MAX(CASE WHEN rn = 5 THEN ClassCost END),
ClassId6 = MAX(CASE WHEN rn = 6 THEN ClassId END), 
ClassName6 = MAX(CASE WHEN rn = 6 THEN ClassName END), 
ClassCost6 = MAX(CASE WHEN rn = 6 THEN ClassCost END)
FROM Numbered
GROUP BY StudentId
)
SELECT S.FirstName, S.LastName, P.*
FROM   Student S
JOIN Pivoted P
ON P.StudentId = S.StudentId