推薦：經(jīng)典SQL語(yǔ)句大全
一、基礎(chǔ) 1、說(shuō)明：創(chuàng)建數(shù)據(jù)庫(kù) CREATE DATABASE database - name 2、說(shuō)明：刪除數(shù)據(jù)庫(kù) drop database dbname 3、說(shuō)明：備份sql server -- - 創(chuàng)建 備份數(shù)據(jù)的 device USE master EXEC sp_addumpdevice ' disk ' , ' testBack ' , ' c:\mssql7backup\MyNwind_1.dat ' --

初始化數(shù)據(jù)：

部門表、員工表數(shù)據(jù)：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]')) 
DROP TABLE [dbo].Department 
GO 
--部門表 
CREATE TABLE Department 
( 
    id int, 
    name nvarchar(50) 
) 
INSERT INTO Department(id,name) 
SELECT 1,'人事部' 
UNION  
SELECT 2,'工程部' 
UNION  
SELECT 3,'管理部' 
 
SELECT * FROM Department 
 
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]')) 
DROP TABLE [dbo].Employee 
GO 
--員工表 
CREATE TABLE Employee 
( 
    id int,  
    name nvarchar(20), 
    deptIds varchar(1000) 
) 
INSERT INTO Employee(id,name,deptIds) 
SELECT 1,'蔣大華','1,2,3' 
UNION 
SELECT 2,'小明','1' 
UNION  
SELECT 3,'小華','' 
 
SELECT * FROM Employee 

希望得到的結(jié)果：

解決方法：

第一步，是得到如下的數(shù)據(jù)。即將員工表集合與相關(guān)的部門集合做交叉連接，其中使用了fun_SplitIds函數(shù)（作用是將ids分割成id列表），然后員工集合與這個(gè)得到的集合做交叉連接

SELECT E.*,ISNULL(D.name,'') AS deptName FROM Employee AS E     OUTER APPLY dbo.fun_SplitIds(E.deptIds) AS DID    LEFT JOIN Department AS D ON DID.ID=D.id;

第二步，已經(jīng)得到了如上的數(shù)據(jù)，然后要做的就是根據(jù)ID分組，并對(duì)deptName列做聚合操作，但可惜的是SQL SERVER還沒有提供對(duì)字符串做聚合的操作。但想到，我們處理樹形結(jié)構(gòu)數(shù)據(jù)時(shí)，用CTE來(lái)做關(guān)系數(shù)據(jù)，做成有樹形格式的數(shù)據(jù)，如此我們也可以將這個(gè)問(wèn)題轉(zhuǎn)換成做樹形格式的問(wèn)題，代碼如下：

WITH EmployeT AS( 
--員工的基本信息(使用OUTER APPLY將多個(gè)ID拆分開來(lái),然后與部門表相關(guān)聯(lián)) 
--此時(shí)已將員工表所存的IDS分別與部門相關(guān)聯(lián),下面需要將此集合中的deptName聚合成一個(gè)記錄 
SELECT E.*,ISNULL(D.name,'') AS deptName 
FROM Employee AS E  
   OUTER APPLY dbo.fun_SplitIds(E.deptIds) AS DID 
   LEFT JOIN Department AS D ON DID.ID=D.id 
),mike AS( 
   SELECT id,name,deptIds,deptName 
      ,ROW_NUMBER()OVER(PARTITION BY id ORDER BY id) AS level_num 
   FROM EmployeT 
),mike2 AS( 
   SELECT id,name,deptIds,CAST(deptName AS NVARCHAR(100)) AS deptName,level_num  
   FROM mike 
   WHERE level_num=1 
   UNION ALL 
   SELECT m.id,m.name,m.deptIds,CAST(m2.deptName+','+m.deptName AS NVARCHAR(100)) AS deptName,m.level_num  
   FROM mike AS m 
      INNER JOIN mike2 AS m2 ON m.ID=m2.id AND m.level_num=m2.level_num+1 
),maxMikeByIDT AS( 
       SELECT id,MAX(level_num) AS level_num 
       FROM mike2  
       GROUP BY ID 
) 
 
SELECT A.id,A.name,A.deptIds,A.deptName 
FROM mike2 AS A  
   INNER JOIN maxMikeByIDT AS B ON A.id=B.ID AND A.level_num=B.level_num 
ORDER BY A.id OPTION (MAXRECURSION 0) 

全部SQL：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]')) 
DROP TABLE [dbo].Department 
GO 
--部門表 
CREATE TABLE Department 
( 
    id int, 
    name nvarchar(50) 
) 
INSERT INTO Department(id,name) 
SELECT 1,'人事部' 
UNION  
SELECT 2,'工程部' 
UNION  
SELECT 3,'管理部' 
 
SELECT * FROM Department 
 
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]')) 
DROP TABLE [dbo].Employee 
GO 
--員工表 
CREATE TABLE Employee 
( 
    id int,  
    name nvarchar(20), 
    deptIds varchar(1000) 
) 
INSERT INTO Employee(id,name,deptIds) 
SELECT 1,'蔣大華','1,2,3' 
UNION 
SELECT 2,'小明','1' 
UNION  
SELECT 3,'小華','' 
 
SELECT * FROM Employee 
 
--創(chuàng)建一個(gè)表值函數(shù)，用來(lái)拆分用逗號(hào)分割的數(shù)字串，返回只有一列數(shù)字的表 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_SplitIds]')) 
DROP FUNCTION [dbo].fun_SplitIds 
GO 
CREATE FUNCTION dbo.fun_SplitIds( 
    @Ids nvarchar(1000) 
) 
RETURNS @t_id TABLE (id VARCHAR(36)) 
AS 
BEGIN 
    DECLARE @i INT,@j INT,@l INT,@v VARCHAR(36); 
    SET @i = 0; 
    SET @j = 0; 
    SET @l = len(@Ids); 
    while(@j < @l) 
    begin 
       SET @j = charindex(',',@Ids,@i+1); 
       IF(@j = 0) set @j = @l+1; 
       SET @v = cast(SUBSTRING(@Ids,@i+1,@j-@i-1) as VARCHAR(36)); 
       INSERT INTO @t_id VALUES(@v) 
       SET @i = @j; 
    END 
    RETURN; 
END 
GO 
 
 
;WITH EmployeT AS( 
--員工的基本信息(使用OUTER APPLY將多個(gè)ID拆分開來(lái),然后與部門表相關(guān)聯(lián)) 
--此時(shí)已將員工表所存的IDS分別與部門相關(guān)聯(lián),下面需要將此集合中的deptName聚合成一個(gè)記錄 
SELECT E.*,ISNULL(D.name,'') AS deptName 
FROM Employee AS E  
   OUTER APPLY dbo.fun_SplitIds(E.deptIds) AS DID 
   LEFT JOIN Department AS D ON DID.ID=D.id 
),mike AS( 
   SELECT id,name,deptIds,deptName 
      ,ROW_NUMBER()OVER(PARTITION BY id ORDER BY id) AS level_num 
   FROM EmployeT 
),mike2 AS( 
   SELECT id,name,deptIds,CAST(deptName AS NVARCHAR(100)) AS deptName,level_num  
   FROM mike 
   WHERE level_num=1 
   UNION ALL 
   SELECT m.id,m.name,m.deptIds,CAST(m2.deptName+','+m.deptName AS NVARCHAR(100)) AS deptName,m.level_num  
   FROM mike AS m 
      INNER JOIN mike2 AS m2 ON m.ID=m2.id AND m.level_num=m2.level_num+1 
),maxMikeByIDT AS( 
       SELECT id,MAX(level_num) AS level_num 
       FROM mike2  
       GROUP BY ID 
) 
 
 
SELECT A.id,A.name,A.deptIds,A.deptName 
FROM mike2 AS A  
   INNER JOIN maxMikeByIDT AS B ON A.id=B.ID AND A.level_num=B.level_num 
ORDER BY A.id OPTION (MAXRECURSION 0) 

分享：SQL Server字符串切割
結(jié)果： 645002*01 --1 45854 --2 183677