--建立辅助表 SET NOCOUNT ON; USE AdventureWorks; GO IFOBJECT_ID('dbo.Nums') ISNOTNULL DROPTABLE dbo.Nums; GO CREATETABLE dbo.Nums(n INTNOTNULLPRIMARYKEY); DECLARE@maxASINT, @rcASINT; SET@max=1000000; SET@rc=1; INSERTINTO Nums VALUES(1); WHILE@rc*2<=@max BEGIN INSERTINTO dbo.Nums SELECT n +@rcFROM dbo.Nums; SET@rc=@rc*2; END INSERTINTO dbo.Nums SELECT n +@rcFROM dbo.Nums WHERE n +@rc<=@max; --示例表 IFOBJECT_ID('dbo.Arrays') ISNOTNULL DROPTABLE dbo.Arrays; GO CREATETABLE dbo.Arrays ( arrid VARCHAR(10) NOTNULLPRIMARYKEY, array VARCHAR(8000) NOTNULL ) INSERTINTO Arrays(arrid, array) VALUES('A', '20,22,25,25,14'); INSERTINTO Arrays(arrid, array) VALUES('B', '30,33,28'); INSERTINTO Arrays(arrid, array) VALUES('C', '12,10,8,12,12,13,12,14,10,9'); INSERTINTO Arrays(arrid, array) VALUES('D', '-4,-6,-4,-2'); --实施方法 SELECT arrid, substring(array,n,charindex(',',array+',',n)-n), n FROM dbo.Arrays JOIN dbo.Nums ON n <=LEN(array) ANDSUBSTRING(','+array, n, 1) =',' /**//* select arrid,charindex(',',array+',',n)-n from dbo.Arrays JOIN dbo.Nums ON n <= LEN(array) AND SUBSTRING(','+array, n, 1) = ',' */ /**//* SELECT arrid, array, n FROM dbo.Arrays JOIN dbo.Nums ON n <= LEN(array) AND SUBSTRING(array, n, 1) = ',' */
--建立辅助表SET NOCOUNT ON;USE AdventureWorks;GOIF OBJECT_ID('dbo.Nums') IS NOT NULL DROP TABLE dbo.Nums;GOCREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);DECLARE @max AS INT, @rc AS INT;SET @max = 100...