?? 3.5.2 字符串并集&交集處理示例.sql
字號:
--示例數據
CREATE TABLE tb(ID int,col varchar(50))
INSERT tb SELECT 1,'1,2,3,4'
UNION ALL SELECT 1,'1,3,4'
UNION ALL SELECT 1,'1,4'
UNION ALL SELECT 2,'11,3,4'
UNION ALL SELECT 2,'1,33,4'
UNION ALL SELECT 3,'1,3,4'
GO
--1. 字符串并集處理函數
CREATE FUNCTION dbo.f_mergSTR(@ID int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @t TABLE(ID int identity,b bit)
--分拆處理輔助表,由于列col的最大寬度為50,所以只需要1到50的分拆輔助記錄
INSERT @t(b) SELECT TOP 50 0
FROM syscolumns
DECLARE @r varchar(50)
SET @r=''
SELECT @r=@r+','+s
FROM(
SELECT s=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
FROM tb a,@t b
WHERE a.ID=@ID
AND b.ID<=LEN(a.col)
AND SUBSTRING(','+a.col,b.ID,1)=','
GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
)a ORDER BY s
RETURN(STUFF(@r,1,1,''))
END
GO
--調用用戶定義實現交并集查詢
SELECT ID,col=dbo.f_mergSTR(ID)
FROM tb
GROUP BY ID
GO
/*================================================================*/
--2. 字符串交集處理函數
CREATE FUNCTION dbo.f_mergSTR(@ID int)
RETURNS varchar(50)
AS
BEGIN
DECLARE @t TABLE(ID int identity,b bit)
--分拆處理輔助表,由于列col的最大寬度為50,所以只需要1到50的分拆輔助記錄
INSERT @t(b) SELECT TOP 50 0
FROM syscolumns
DECLARE @r varchar(50)
SET @r=''
SELECT @r=@r+','+s
FROM(
SELECT s=SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
FROM tb a,@t b
WHERE a.ID=@ID
AND b.ID<=LEN(a.col)
AND SUBSTRING(','+a.col,b.ID,1)=','
GROUP BY SUBSTRING(a.col,b.ID,CHARINDEX(',',a.col+',',b.ID)-b.ID)
HAVING COUNT(*)=(SELECT COUNT(*) FROM tb WHERE ID=@ID))a
RETURN(STUFF(@r,1,1,''))
END
GO
--調用用戶定義實現交交集查詢
SELECT ID,col=dbo.f_mergStr(ID)
FROM tb
GROUP BY ID
GO
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -