菜鸟写法 DECLARE @i INT ,@IDs VARCHAR(100),@Parmeters VARCHAR(100) select @IDs ='101,102,103,104,105,106', @Parmeters = 'Freq,PN,TotalEcIo,TxAGC,RxAGC,FFER'if object_id('ids') IS NOT NULL drop table ids create table ids (id int) while charindex(',',@IDs) > 0 BEGIN insert into ids values(cast(substring(@IDs,1,charindex(',',@IDs)-1) as int)) SET @IDs = right(@IDs,len(@IDs)-charindex(',',@IDs)) ENDif object_id('para') IS NOT NULL drop table para create table para(parmeter varchar(8)) while charindex(',',@Parmeters) > 0 BEGIN insert into para values(substring(@Parmeters,1,charindex(',',@Parmeters)-1)) SET @Parmeters = right(@Parmeters,len(@Parmeters)-charindex(',',@Parmeters)) END select * from ids, para
--1)代码段SET NOCOUNT ONIF(OBJECT_ID('Test') IS NOT NULL) DROP TABLE test CREATE TABLE Test ( ID INT, Paramter VARCHAR(10) ) DECLARE @IDs varchar(MAX) DECLARE @Parmeters varchar(MAX) DECLARE @iCount_ID int DECLARE @ilength_ID int DECLARE @ID nvarchar(200)DECLARE @iCount_Parmeter int DECLARE @ilength_Parmeter int DECLARE @Parmeter nvarchar(200) DECLARE @Parmeters_Bak nvarchar(200)SET @IDs ='101,102,103,104,105,106' SET @Parmeters = 'Freq,PN,TotalEcIo,TxAGC,RxAGC,FFER' SET @Parmeters_Bak = @Parmeters SET @iCount_ID = 1 SET @iCount_Parmeter = 1--DECLARE @IDList table(ID nvarchar(30),Parmeter nvarchar(30)) --亦可表变量 WHILE @iCount_ID <> 0 BEGIN SET @iCount_ID = PATINDEX('%,%',@IDs) SET @ilength_ID = CASE @iCount_ID WHEN 0 THEN DATALENGTH(@IDs) ELSE @iCount_ID - 1 END SET @ID = SUBSTRING(@IDs,1,@ilength_ID) WHILE @iCount_Parmeter <> 0 BEGIN SET @iCount_Parmeter = PATINDEX('%,%',@Parmeters) SET @ilength_Parmeter = CASE @iCount_Parmeter WHEN 0 THEN DATALENGTH(@Parmeters) ELSE @iCount_Parmeter - 1 END
SET @Parmeter = SUBSTRING(@Parmeters,1,@ilength_Parmeter)-- INSERT INTO @IDList VALUES(@ID,@Parmeter) ---亦可表变量 INSERT INTO Test VALUES(CAST(@ID AS int),@Parmeter) SET @Parmeters = SUBSTRING(@Parmeters,@iCount_Parmeter + 1,DATALENGTH(@Parmeters)) END SET @IDs = SUBSTRING(@IDs,@iCount_ID + 1,DATALENGTH(@IDs)) SET @iCount_Parmeter = 1 SET @Parmeters =@Parmeters_Bak ENDSELECT * FROM Test --2)结果ID Paramter ----------- ---------- 101 Freq 101 PN 101 TotalEcIo 101 TxAGC 101 RxAGC 101 FFER 102 Freq 102 PN 102 TotalEcIo 102 TxAGC 102 RxAGC 102 FFER 103 Freq 103 PN 103 TotalEcIo 103 TxAGC 103 RxAGC 103 FFER 104 Freq 104 PN 104 TotalEcIo 104 TxAGC 104 RxAGC 104 FFER 105 Freq 105 PN 105 TotalEcIo 105 TxAGC 105 RxAGC 105 FFER 106 Freq 106 PN 106 TotalEcIo 106 TxAGC 106 RxAGC 106 FFER(36 row(s) affected)
--1)创建拆分函数CREATE FUNCTION f_splitSTR( @s varchar(8000), --待分拆的字符串 @split varchar(10) --数据分隔符 )RETURNS @re TABLE(col varchar(100)) AS BEGIN DECLARE @splitlen int SET @splitlen=LEN(@split+'a')-2 WHILE CHARINDEX(@split,@s)>0 BEGIN INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1)) SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'') END INSERT @re VALUES(@s) RETURN END GO--2)测试查询DECLARE @IDs varchar(MAX) DECLARE @Parmeters varchar(MAX)SET @IDs ='101,102,103,104,105,106' SET @Parmeters = 'Freq,PN,TotalEcIo,TxAGC,RxAGC,FFER'--SELECT * FROM f_splitSTR(@IDs,',') A,f_splitSTR(@Parmeters,',') B --实为笛卡尔积,见下 --ORDER BY A.col-- ORSELECT * FROM f_splitSTR(@IDs,',') A CROSS JOIN f_splitSTR(@Parmeters,',') B ORDER BY A.col --3)结果col col ------------------- 101 Freq 101 PN 101 TotalEcIo 101 TxAGC 101 RxAGC 101 FFER 102 Freq 102 PN 102 TotalEcIo 102 TxAGC 102 RxAGC 102 FFER 103 Freq 103 PN 103 TotalEcIo 103 TxAGC 103 RxAGC 103 FFER 104 Freq 104 PN 104 TotalEcIo 104 TxAGC 104 RxAGC 104 FFER 105 Freq 105 PN 105 TotalEcIo 105 TxAGC 105 RxAGC 105 FFER 106 Freq 106 PN 106 TotalEcIo 106 TxAGC 106 RxAGC 106 FFER(36 row(s) affected)
SELECT ID,Parmeter FROM ( SELECT ID = x.n.value('.','int') FROM ( SELECT ValuesXML = CAST('<root>' + REPLACE((SELECT v = @IDs FOR XML PATH('')),',','</v><v>') + '</root>' AS XML) ) t CROSS APPLY t.ValuesXML.nodes('/root/v') x(n) ) A CROSS JOIN ( SELECT Parmeter = x.n.value('.','varchar(10)') FROM ( SELECT ValuesXML = CAST('<root>' + REPLACE((SELECT v = @Parmeters FOR XML PATH('')),',','</v><v>') + '</root>' AS XML) ) t CROSS APPLY t.ValuesXML.nodes('/root/v') x(n) ) B ORDER BY ID,Parmeter
DECLARE @IDs varchar(100), @Parameters varchar(100),@MidParam VARCHAR(1010)SET @IDs ='101,102,103,104,105,106' SET @Parameters = 'Freq,PN,TotalEcIo,TxAGC,RxAGC,FFER' SET @MidParam = @Parameters --SELECT CHARINDEX(',',@IDs) --SELECT RIGHT(CHARINDEX(',',@IDs),LEN(@IDs)) IF(RIGHT(@ids,1)!=',') SET @IDs=@IDs+',' IF(RIGHT(@Parameters,1)!=',') SET @Parameters=@Parameters+',' WHILE (CHARINDEX(',',@IDs)>0) BEGIN WHILE (CHARINDEX(',',@Parameters)>0) BEGIN PRINT 'ID:'+LEFT(@IDs,charindex(',',@IDs)-1)+';Param:'+LEFT(@Parameters,charindex(',',@Parameters)-1)
SET @Parameters = right(@Parameters,len(@Parameters)-charindex(',',@Parameters)) END SET @Parameters = @MidParam SET @IDs = right(@IDs,len(@IDs)-charindex(',',@IDs)) END双重循环。
DECLARE @IDs varchar(100), @Parameters varchar(100),@MidParam VARCHAR(100),@i INT SET @IDs ='101,102,103,104,105,106' SET @Parameters = 'Freq,PN,TotalEcIo,TxAGC,RxAGC,FFER' SET @MidParam = @Parameters --SELECT CHARINDEX(',',@IDs) --SELECT RIGHT(CHARINDEX(',',@IDs),LEN(@IDs)) IF(RIGHT(@ids,1)!=',') SET @IDs=@IDs+',' IF(RIGHT(@Parameters,1)!=',') SET @Parameters=@Parameters+',' WHILE (CHARINDEX(',',@IDs)>0) BEGIN SET @i = 1 PRINT 'ID:'+LEFT(@IDs,charindex(',',@IDs)-1) WHILE (CHARINDEX(',',@Parameters)>0) BEGIN PRINT 'Param:'+ LEFT(@Parameters,charindex(',',@Parameters)-1)+';Order:'+CONVERT(VARCHAR,@i) SET @Parameters = right(@Parameters,len(@Parameters)-charindex(',',@Parameters)) SET @i=@i+1 END SET @Parameters = @MidParam SET @IDs = right(@IDs,len(@IDs)-charindex(',',@IDs)) END
select
@IDs ='101,102,103,104,105,106',
@Parmeters = 'Freq,PN,TotalEcIo,TxAGC,RxAGC,FFER'
SELECT @IDs='SELECT ID='+REPLACE(@IDs,',',' UNION ALL SELECT ')SELECT @Parmeters='SELECT ID='''+REPLACE(@Parmeters,',',''' UNION ALL SELECT ''')+''''SELECT @SQL='SELECT * FROM ('+@IDs+') A ,('+@Parmeters+') B '
EXEC( @SQL)ID ID
----------- ---------
101 Freq
101 PN
101 TotalEcIo
101 TxAGC
101 RxAGC
101 FFER
102 Freq
102 PN
102 TotalEcIo
102 TxAGC
102 RxAGC
102 FFER
103 Freq
103 PN
103 TotalEcIo
103 TxAGC
103 RxAGC
103 FFER
104 Freq
104 PN
104 TotalEcIo
104 TxAGC
104 RxAGC
104 FFER
105 Freq
105 PN
105 TotalEcIo
105 TxAGC
105 RxAGC
105 FFER
106 Freq
106 PN
106 TotalEcIo
106 TxAGC
106 RxAGC
106 FFER
DECLARE @i INT ,@IDs VARCHAR(100),@Parmeters VARCHAR(100)
select
@IDs ='101,102,103,104,105,106',
@Parmeters = 'Freq,PN,TotalEcIo,TxAGC,RxAGC,FFER'if object_id('ids') IS NOT NULL
drop table ids
create table ids (id int)
while charindex(',',@IDs) > 0
BEGIN
insert into ids values(cast(substring(@IDs,1,charindex(',',@IDs)-1) as int))
SET @IDs = right(@IDs,len(@IDs)-charindex(',',@IDs))
ENDif object_id('para') IS NOT NULL
drop table para
create table para(parmeter varchar(8))
while charindex(',',@Parmeters) > 0
BEGIN
insert into para values(substring(@Parmeters,1,charindex(',',@Parmeters)-1))
SET @Parmeters = right(@Parmeters,len(@Parmeters)-charindex(',',@Parmeters))
END
select * from ids, para
/*
id parmeter
----------- ----------------------------
101 Freq
102 Freq
103 Freq
104 Freq
105 Freq
101 PN
102 PN
103 PN
104 PN
....(25 行受影响)
*/
DROP TABLE test
CREATE TABLE Test
(
ID INT,
Paramter VARCHAR(10)
)
DECLARE @IDs varchar(MAX)
DECLARE @Parmeters varchar(MAX)
DECLARE @iCount_ID int
DECLARE @ilength_ID int
DECLARE @ID nvarchar(200)DECLARE @iCount_Parmeter int
DECLARE @ilength_Parmeter int
DECLARE @Parmeter nvarchar(200)
DECLARE @Parmeters_Bak nvarchar(200)SET @IDs ='101,102,103,104,105,106'
SET @Parmeters = 'Freq,PN,TotalEcIo,TxAGC,RxAGC,FFER'
SET @Parmeters_Bak = @Parmeters
SET @iCount_ID = 1
SET @iCount_Parmeter = 1--DECLARE @IDList table(ID nvarchar(30),Parmeter nvarchar(30)) --亦可表变量
WHILE @iCount_ID <> 0
BEGIN
SET @iCount_ID = PATINDEX('%,%',@IDs)
SET @ilength_ID = CASE @iCount_ID WHEN 0 THEN DATALENGTH(@IDs) ELSE @iCount_ID - 1 END
SET @ID = SUBSTRING(@IDs,1,@ilength_ID) WHILE @iCount_Parmeter <> 0
BEGIN
SET @iCount_Parmeter = PATINDEX('%,%',@Parmeters)
SET @ilength_Parmeter = CASE @iCount_Parmeter WHEN 0 THEN DATALENGTH(@Parmeters) ELSE @iCount_Parmeter - 1 END
SET @Parmeter = SUBSTRING(@Parmeters,1,@ilength_Parmeter)-- INSERT INTO @IDList VALUES(@ID,@Parmeter) ---亦可表变量 INSERT INTO Test VALUES(CAST(@ID AS int),@Parmeter)
SET @Parmeters = SUBSTRING(@Parmeters,@iCount_Parmeter + 1,DATALENGTH(@Parmeters))
END
SET @IDs = SUBSTRING(@IDs,@iCount_ID + 1,DATALENGTH(@IDs))
SET @iCount_Parmeter = 1
SET @Parmeters =@Parmeters_Bak
ENDSELECT * FROM Test
--2)结果ID Paramter
----------- ----------
101 Freq
101 PN
101 TotalEcIo
101 TxAGC
101 RxAGC
101 FFER
102 Freq
102 PN
102 TotalEcIo
102 TxAGC
102 RxAGC
102 FFER
103 Freq
103 PN
103 TotalEcIo
103 TxAGC
103 RxAGC
103 FFER
104 Freq
104 PN
104 TotalEcIo
104 TxAGC
104 RxAGC
104 FFER
105 Freq
105 PN
105 TotalEcIo
105 TxAGC
105 RxAGC
105 FFER
106 Freq
106 PN
106 TotalEcIo
106 TxAGC
106 RxAGC
106 FFER(36 row(s) affected)
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO--2)测试查询DECLARE @IDs varchar(MAX)
DECLARE @Parmeters varchar(MAX)SET @IDs ='101,102,103,104,105,106'
SET @Parmeters = 'Freq,PN,TotalEcIo,TxAGC,RxAGC,FFER'--SELECT * FROM f_splitSTR(@IDs,',') A,f_splitSTR(@Parmeters,',') B --实为笛卡尔积,见下
--ORDER BY A.col-- ORSELECT * FROM f_splitSTR(@IDs,',') A
CROSS JOIN f_splitSTR(@Parmeters,',') B
ORDER BY A.col
--3)结果col col
-------------------
101 Freq
101 PN
101 TotalEcIo
101 TxAGC
101 RxAGC
101 FFER
102 Freq
102 PN
102 TotalEcIo
102 TxAGC
102 RxAGC
102 FFER
103 Freq
103 PN
103 TotalEcIo
103 TxAGC
103 RxAGC
103 FFER
104 Freq
104 PN
104 TotalEcIo
104 TxAGC
104 RxAGC
104 FFER
105 Freq
105 PN
105 TotalEcIo
105 TxAGC
105 RxAGC
105 FFER
106 Freq
106 PN
106 TotalEcIo
106 TxAGC
106 RxAGC
106 FFER(36 row(s) affected)
SELECT ID,Parmeter
FROM (
SELECT ID = x.n.value('.','int')
FROM (
SELECT ValuesXML = CAST('<root>' +
REPLACE((SELECT v = @IDs FOR XML PATH('')),',','</v><v>') +
'</root>' AS XML)
) t
CROSS APPLY t.ValuesXML.nodes('/root/v') x(n)
) A
CROSS JOIN (
SELECT Parmeter = x.n.value('.','varchar(10)')
FROM (
SELECT ValuesXML = CAST('<root>' +
REPLACE((SELECT v = @Parmeters FOR XML PATH('')),',','</v><v>') +
'</root>' AS XML)
) t
CROSS APPLY t.ValuesXML.nodes('/root/v') x(n)
) B
ORDER BY ID,Parmeter
SET @Parameters = 'Freq,PN,TotalEcIo,TxAGC,RxAGC,FFER'
SET @MidParam = @Parameters
--SELECT CHARINDEX(',',@IDs)
--SELECT RIGHT(CHARINDEX(',',@IDs),LEN(@IDs))
IF(RIGHT(@ids,1)!=',') SET @IDs=@IDs+','
IF(RIGHT(@Parameters,1)!=',') SET @Parameters=@Parameters+','
WHILE (CHARINDEX(',',@IDs)>0)
BEGIN
WHILE (CHARINDEX(',',@Parameters)>0)
BEGIN
PRINT 'ID:'+LEFT(@IDs,charindex(',',@IDs)-1)+';Param:'+LEFT(@Parameters,charindex(',',@Parameters)-1)
SET @Parameters = right(@Parameters,len(@Parameters)-charindex(',',@Parameters))
END
SET @Parameters = @MidParam
SET @IDs = right(@IDs,len(@IDs)-charindex(',',@IDs))
END双重循环。
DECLARE @IDs varchar(100), @Parameters varchar(100),@MidParam VARCHAR(100),@i INT SET @IDs ='101,102,103,104,105,106'
SET @Parameters = 'Freq,PN,TotalEcIo,TxAGC,RxAGC,FFER'
SET @MidParam = @Parameters
--SELECT CHARINDEX(',',@IDs)
--SELECT RIGHT(CHARINDEX(',',@IDs),LEN(@IDs))
IF(RIGHT(@ids,1)!=',') SET @IDs=@IDs+','
IF(RIGHT(@Parameters,1)!=',') SET @Parameters=@Parameters+','
WHILE (CHARINDEX(',',@IDs)>0)
BEGIN
SET @i = 1
PRINT 'ID:'+LEFT(@IDs,charindex(',',@IDs)-1)
WHILE (CHARINDEX(',',@Parameters)>0)
BEGIN
PRINT 'Param:'+
LEFT(@Parameters,charindex(',',@Parameters)-1)+';Order:'+CONVERT(VARCHAR,@i)
SET @Parameters = right(@Parameters,len(@Parameters)-charindex(',',@Parameters))
SET @i=@i+1
END
SET @Parameters = @MidParam
SET @IDs = right(@IDs,len(@IDs)-charindex(',',@IDs))
END