mssql多表id汇总:一招制胜

介绍

MSSQL中处理多表ID汇总,对于Oracle或者MySQL来说,一般可以通过GROUP_CONCAT函数来实现,而在MSSQL中则需要使用FOR XML PATH函数来实现。下面我们就来介绍一招制胜的方法。

具体步骤

步骤一:准备测试数据

首先,我们需要准备测试数据,建立两张表,表1为students(学生表),表2为courses(课程表),并且建立一个关联表sc(学生选课表),用来维护学生和课程的关联关系,建表语句如下:

CREATE TABLE students(

id INT PRIMARY KEY,

name VARCHAR(20)

)

CREATE TABLE courses(

id INT PRIMARY KEY,

name VARCHAR(20),

credit INT

)

CREATE TABLE sc(

sid INT,

cid INT,

PRIMARY KEY(sid,cid)

)

然后插入测试数据,用于验证后续的SQL语句:

INSERT INTO students(id,name)

VALUES(1,'Tom'),

(2,'Jack'),

(3,'Lucy'),

(4,'Mary'),

(5,'Jerry')

INSERT INTO courses(id,name,credit)

VALUES(1,'Math',3),

(2,'English',2),

(3,'Art',1),

(4,'Music',1),

(5,'Computer',4)

INSERT INTO sc(sid,cid)

VALUES(1,1),

(1,2),

(1,5),

(2,2),

(2,3),

(2,5),

(3,3),

(3,4),

(3,5),

(4,1),

(4,2),

(4,5),

(5,1),

(5,3),

(5,5)

步骤二:使用FOR XML PATH实现多表ID汇总

下面我们通过FOR XML PATH函数实现学生和课程ID的汇总,SQL语句如下:

SELECT s.id as sid, s.name as sname,

STUFF((SELECT ',' + CAST(c.id as VARCHAR(10))

FROM courses c JOIN sc ON c.id = sc.cid

WHERE sc.sid = s.id

FOR XML PATH('')),1,1,'') AS cids

FROM students s

解析上述SQL语句:STUFF函数可以将汇总的ID字符串中的第一个逗号替换掉,CAST函数将ID转换为字符串。这样,我们就实现了通过FOR XML PATH函数来汇总多表ID的目的。

步骤三:使用WITH TIES实现其他统计

如果我们需要汇总学生选课的学分和,则可以使用WITH TIES子句来实现,SQL语句如下:

SELECT TOP 1 WITH TIES s.id as sid, s.name as sname,

STUFF((SELECT ',' + CAST(c.id as VARCHAR(10))

FROM courses c JOIN sc ON c.id = sc.cid

WHERE sc.sid = s.id

FOR XML PATH('')),1,1,'') AS cids,

SUM(c.credit) OVER (PARTITION BY s.id) AS ccredit

FROM students s JOIN sc ON s.id = sc.sid

JOIN courses c ON c.id = sc.cid

ORDER BY ccredit DESC

解析上述SQL语句:通过SUM函数实现学生选课的学分和,并且使用OVER子句指定分组,使用WITH TIES子句来获取学分和最高的学生。

总结

通过以上三个步骤,我们成功地实现了通过FOR XML PATH函数来汇总MSSQL数据库中的多表ID,并且通过WITH TIES子句来实现其他统计需求。这一招制胜的方法,为我们在MSSQL数据库中处理多表数据提供了极大的便利和高效性。

免责声明:本文来自互联网,本站所有信息(包括但不限于文字、视频、音频、数据及图表),不保证该信息的准确性、真实性、完整性、有效性、及时性、原创性等,版权归属于原作者,如无意侵犯媒体或个人知识产权,请来电或致函告之,本站将在第一时间处理。猿码集站发布此文目的在于促进信息交流,此文观点与本站立场无关,不承担任何责任。

数据库标签