时间戳

首页 > 解决方案 > 如何在sql server中将连接名称作为连接ID

sql - 如何在sql server中将连接名称作为连接ID

问题描述

我有学生查找表

学生桌

Student id  Student name
1               Sonu
2               Janu
3               Sameer
4               Shri

课程表

Course Name  Student Id
JAVA          1,2,3
SQL            2,3
HTML           3,4
PHP            2,1

我有另一个表，即课程表，在那个表中我只是输入带有逗号分隔值的学生 ID。现在我想使用课程表中的学生 ID 获取学生姓名，并且学生姓名应该与存储在课程表中的学生 ID 的顺序相同

结果应该是

Course Name  Student Name
JAVA           Sonu,Janu,Sameer
SQL            Janu, Sameer
HTML           Sameer,Shri
PHP            Janu,Sonu

任何人都可以帮助解决这个问题

我的 sql 版本是：Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Developer Edition (64-bit) o​​n Windows NT 6.3 (Build 9600:) (Hypervisor)

标签： sqlsql-servertsqlsql-server-2014

解决方案

您正在寻找的是典型的m:n关系。

你有学生，你有课程。两者都是独立存在的。现在您要存储哪个学生上哪个课程。因此，您需要一个映射表：

CREATE TABLE Student(ID INT NOT NULL CONSTRAINT PK_Student PRIMARY KEY
                    ,FirstName NVARCHAR(200) NOT NULL
                    ,LastName NVARCHAR(200) NOT NULL);

INSERT INTO Student(ID,FirstName,LastName) 
                    VALUES(1,'Sonu','Jones')
                         ,(2,'Janu','Smith')
                         ,(3,'Sameer','Miller')
                         ,(4,'Shri','Wilson');

CREATE TABLE Course(ID INT NOT NULL CONSTRAINT PK_Course PRIMARY KEY
                   ,Title NVARCHAR(200) NOT NULL);

INSERT INTO Course(ID,Title) 
                   VALUES(1,'JAVA')
                        ,(2,'SQL')
                        ,(3,'HTML')
                        ,(4,'PHP');

CREATE TABLE CourseStudent(ID INT IDENTITY CONSTRAINT PK_CourseStudent PRIMARY KEY
                          ,CourseID INT NOT NULL CONSTRAINT FK_CourseID FOREIGN KEY REFERENCES Course(ID)
                          ,StudentID INT NOT NULL CONSTRAINT FK_StudentID FOREIGN KEY REFERENCES Student(ID));

INSERT INTO CourseStudent VALUES(1,1),(1,2),(1,3) --Course 1 is taken by 1,2,3
                               ,(2,2),(2,3)       --Course 2 is taken by 2 and 3
                               ,(3,3),(3,4)
                               ,(4,2),(4,1);

select--这是您用来获取映射数据的典型方法

SELECT c.Title
      ,s.FirstName
FROM Course AS c
INNER JOIN CourseStudent AS cs ON c.ID=cs.CourseID
INNER JOIN Student AS s ON s.ID=cs.StudentID

--这是您用来将参加的学生作为连接字符串的选择：

SELECT c.Title
      ,STUFF(
              (
                SELECT ', ' + s.FirstName 
                FROM CourseStudent AS cs
                INNER JOIN Student AS s ON cs.StudentID=s.ID 
                WHERE cs.CourseID=c.ID
                FOR XML PATH(''),TYPE
              ).value('.','nvarchar(max)'),1,2,''
            ) AS StudentName
FROM Course AS c;

我在 XML 中使用了一个相当奇怪的技巧来实现分组字符串连接。为此引入了 SQL-Server 2017+ STRING_AGG()。

推荐阅读