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数据库中处理多表数据提供了极大的便利和高效性。