一列保存多個ID(將多個用逗號隔開的ID轉換成用逗號隔開的名稱)_Mssql數據庫教程

      編輯Tag賺U幣
      教程Tag:暫無Tag,歡迎添加,賺取U幣!

      推薦:經典SQL語句大全
      一、基礎 1、說明:創建數據庫 CREATE DATABASE database - name 2、說明:刪除數據庫 drop database dbname 3、說明:備份sql server -- - 創建 備份數據的 device USE master EXEC sp_addumpdevice ' disk ' , ' testBack ' , ' c:\mssql7backup\MyNwind_1.dat ' --

      背景:在做項目時,經常會遇到這樣的表結構在主表的中有一列保存的是用逗號隔開ID。如,當一個員工從屬多個部門時、當一個項目從屬多個城市時、當一個設備從屬多個項目時,很多人都會在員工表中加入一個deptIds VARCHAR(1000)列(本文以員工從屬多個部門為例),用以保存部門編號列表(很明顯這不符合第一范式,但很多人這樣設計了,在這篇文章中我們暫不討論在這種應用場景下,如此設計的對與錯,有興趣的可以在回復中聊聊),然后我們在查詢列表中需要看到這個員工從屬哪些部門。

      初始化數據:

      部門表、員工表數據:

      1. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]')) 
      2. DROP TABLE [dbo].Department 
      3. GO 
      4. --部門表 
      5. CREATE TABLE Department 
      6.     id int
      7.     name nvarchar(50) 
      8. INSERT INTO Department(id,name
      9. SELECT 1,'人事部' 
      10. UNION  
      11. SELECT 2,'工程部' 
      12. UNION  
      13. SELECT 3,'管理部' 
      14.  
      15. SELECT * FROM Department 
      16.  
      17.  
      18. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]')) 
      19. DROP TABLE [dbo].Employee 
      20. GO 
      21. --員工表 
      22. CREATE TABLE Employee 
      23.     id int,  
      24.     name nvarchar(20), 
      25.     deptIds varchar(1000) 
      26. INSERT INTO Employee(id,name,deptIds) 
      27. SELECT 1,'蔣大華','1,2,3' 
      28. UNION 
      29. SELECT 2,'小明','1' 
      30. UNION  
      31. SELECT 3,'小華','' 
      32.  
      33. SELECT * FROM Employee 

      希望得到的結果:

      解決方法:

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

      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;

       

      第二步,已經得到了如上的數據,然后要做的就是根據ID分組,并對deptName列做聚合操作,但可惜的是SQL SERVER還沒有提供對字符串做聚合的操作。但想到,我們處理樹形結構數據時,用CTE來做關系數據,做成有樹形格式的數據,如此我們也可以將這個問題轉換成做樹形格式的問題,代碼如下:

      1. WITH EmployeT AS
      2. --員工的基本信息(使用OUTER APPLY將多個ID拆分開來,然后與部門表相關聯) 
      3. --此時已將員工表所存的IDS分別與部門相關聯,下面需要將此集合中的deptName聚合成一個記錄 
      4. SELECT E.*,ISNULL(D.name,''AS deptName 
      5. FROM Employee AS E  
      6.    OUTER APPLY dbo.fun_SplitIds(E.deptIds) AS DID 
      7.    LEFT JOIN Department AS D ON DID.ID=D.id 
      8. ),mike AS
      9.    SELECT id,name,deptIds,deptName 
      10.       ,ROW_NUMBER()OVER(PARTITION BY id ORDER BY id) AS level_num 
      11.    FROM EmployeT 
      12. ),mike2 AS
      13.    SELECT id,name,deptIds,CAST(deptName AS NVARCHAR(100)) AS deptName,level_num  
      14.    FROM mike 
      15.    WHERE level_num=1 
      16.    UNION ALL 
      17.    SELECT m.id,m.name,m.deptIds,CAST(m2.deptName+','+m.deptName AS NVARCHAR(100)) AS deptName,m.level_num  
      18.    FROM mike AS m 
      19.       INNER JOIN mike2 AS m2 ON m.ID=m2.id AND m.level_num=m2.level_num+1 
      20. ),maxMikeByIDT AS
      21.        SELECT id,MAX(level_num) AS level_num 
      22.        FROM mike2  
      23.        GROUP BY ID 
      24.  
      25. SELECT A.id,A.name,A.deptIds,A.deptName 
      26. FROM mike2 AS A  
      27.    INNER JOIN maxMikeByIDT AS B ON A.id=B.ID AND A.level_num=B.level_num 
      28. ORDER BY A.id OPTION (MAXRECURSION 0) 
      結果如下:

      全部SQL:

      1. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]')) 
      2. DROP TABLE [dbo].Department 
      3. GO 
      4. --部門表 
      5. CREATE TABLE Department 
      6.     id int
      7.     name nvarchar(50) 
      8. INSERT INTO Department(id,name
      9. SELECT 1,'人事部' 
      10. UNION  
      11. SELECT 2,'工程部' 
      12. UNION  
      13. SELECT 3,'管理部' 
      14.  
      15. SELECT * FROM Department 
      16.  
      17.  
      18. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]')) 
      19. DROP TABLE [dbo].Employee 
      20. GO 
      21. --員工表 
      22. CREATE TABLE Employee 
      23.     id int,  
      24.     name nvarchar(20), 
      25.     deptIds varchar(1000) 
      26. INSERT INTO Employee(id,name,deptIds) 
      27. SELECT 1,'蔣大華','1,2,3' 
      28. UNION 
      29. SELECT 2,'小明','1' 
      30. UNION  
      31. SELECT 3,'小華','' 
      32.  
      33. SELECT * FROM Employee 
      34.  
      35. --創建一個表值函數,用來拆分用逗號分割的數字串,返回只有一列數字的表 
      36. IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_SplitIds]')) 
      37. DROP FUNCTION [dbo].fun_SplitIds 
      38. GO 
      39. CREATE FUNCTION dbo.fun_SplitIds( 
      40.     @Ids nvarchar(1000) 
      41. RETURNS @t_id TABLE (id VARCHAR(36)) 
      42. AS 
      43. BEGIN 
      44.     DECLARE @i INT,@j INT,@l INT,@v VARCHAR(36); 
      45.     SET @i = 0; 
      46.     SET @j = 0; 
      47.     SET @l = len(@Ids); 
      48.     while(@j < @l) 
      49.     begin 
      50.        SET @j = charindex(',',@Ids,@i+1); 
      51.        IF(@j = 0) set @j = @l+1; 
      52.        SET @v = cast(SUBSTRING(@Ids,@i+1,@j-@i-1) as VARCHAR(36)); 
      53.        INSERT INTO @t_id VALUES(@v) 
      54.        SET @i = @j; 
      55.     END 
      56.     RETURN
      57. END 
      58. GO 
      59.  
      60.  
      61. ;WITH EmployeT AS
      62. --員工的基本信息(使用OUTER APPLY將多個ID拆分開來,然后與部門表相關聯) 
      63. --此時已將員工表所存的IDS分別與部門相關聯,下面需要將此集合中的deptName聚合成一個記錄 
      64. SELECT E.*,ISNULL(D.name,''AS deptName 
      65. FROM Employee AS E  
      66.    OUTER APPLY dbo.fun_SplitIds(E.deptIds) AS DID 
      67.    LEFT JOIN Department AS D ON DID.ID=D.id 
      68. ),mike AS
      69.    SELECT id,name,deptIds,deptName 
      70.       ,ROW_NUMBER()OVER(PARTITION BY id ORDER BY id) AS level_num 
      71.    FROM EmployeT 
      72. ),mike2 AS
      73.    SELECT id,name,deptIds,CAST(deptName AS NVARCHAR(100)) AS deptName,level_num  
      74.    FROM mike 
      75.    WHERE level_num=1 
      76.    UNION ALL 
      77.    SELECT m.id,m.name,m.deptIds,CAST(m2.deptName+','+m.deptName AS NVARCHAR(100)) AS deptName,m.level_num  
      78.    FROM mike AS m 
      79.       INNER JOIN mike2 AS m2 ON m.ID=m2.id AND m.level_num=m2.level_num+1 
      80. ),maxMikeByIDT AS
      81.        SELECT id,MAX(level_num) AS level_num 
      82.        FROM mike2  
      83.        GROUP BY ID 
      84.  
      85.  
      86. SELECT A.id,A.name,A.deptIds,A.deptName 
      87. FROM mike2 AS A  
      88.    INNER JOIN maxMikeByIDT AS B ON A.id=B.ID AND A.level_num=B.level_num 
      89. ORDER BY A.id OPTION (MAXRECURSION 0) 

      分享:SQL Server字符串切割
      結果: 645002*01 --1 45854 --2 183677

      來源:未知//所屬分類:Mssql數據庫教程/更新時間:2012-07-20
      相關Mssql數據庫教程