?? 5.4.3 返回已用編號、缺號分布字符串的處理示例.sql
字號:
--生成已用編號分布字符串的函數
CREATE FUNCTION f_GetStrSeries(@col1 varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @re varchar(8000),@pid int
SELECT @re='',@pid=-1
SELECT @re=CASE
WHEN col2=@pid+1 THEN @re
ELSE @re
+CASE
WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
ELSE CAST(-@pid as varchar)
END
+','+CAST(col2 as varchar)
END,
@pid=col2
FROM tb
WHERE col1=@col1
ORDER BY col2
RETURN(STUFF(@re,1,2,'')
+CASE
WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''
ELSE CAST(-@pid as varchar)
END)
END
GO
--生成缺號分布字符串的函數
CREATE FUNCTION f_GetStrNSeries(@col1 varchar(10))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @re varchar(8000),@pid int
SELECT @re='',@pid=0
SELECT @re=CASE
WHEN col2=@pid+1 THEN @re
ELSE @re+','+CAST(@pid+1 as varchar)
+CASE
WHEN @pid+1=col2-1 THEN ''
ELSE CAST(1-col2 as varchar)
END
END,
@pid=col2
FROM tb
WHERE col1=@col1
ORDER BY col2
RETURN(STUFF(@re,1,1,''))
END
GO
--調用測試
--測試數據
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',2
UNION ALL SELECT 'a',3
UNION ALL SELECT 'a',5
UNION ALL SELECT 'a',8
UNION ALL SELECT 'a',9
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',5
UNION ALL SELECT 'b',6
UNION ALL SELECT 'b',7
--查詢
SELECT col1,
col2_Series=dbo.f_GetStrSeries(col1),
col2_Series=dbo.f_GetStrNSeries(col1)
FROM tb
GROUP BY col1
/*--結果
col1 col2_Series col2_Series
-------------- ------------------------ --------------
a 2-3,5,8-9 1,4,6-7
b 1,5-7 2-4
--*/
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -