目前我是用这个实现的,感觉效率不高,请各位再看看有什么可以效率高点的写法:
DECLARE @DepAirport nvarchar(200)
DECLARE @DesAirport nvarchar(200)set @DepAirport ='UA,NW,CX,CO'
set @DesAirport ='UA,NW,CX,CO,DL,AA'DECLARE @CityCode table( cityCode nvarchar(10) collate Latin1_General_BIN)IF len(@DepAirport)>3
BEGIN
SET @DepAirport = ISNULL( @DepAirport,'')+ ','
WHILE CHARINDEX(',',@DepAirport) >0
BEGIN
INSERT INTO @CityCode(cityCode) Values(LEFT (@DepAirport ,CHARINDEX(',',@DepAirport)-1))
SET @DepAirport =STUFF (@DepAirport,1 , CHARINDEX(',',@DepAirport),'')
END
END
ELSE
INSERT INTO @CityCode(cityCode) Values(@DepAirport)IF len(@DesAirport)>3
BEGIN
SET @DesAirport = ISNULL( @DesAirport,'')+ ','
WHILE CHARINDEX(',',@DesAirport) >0
BEGIN
INSERT INTO @CityCode(cityCode) Values(LEFT (@DesAirport ,CHARINDEX(',',@DesAirport)-1))
SET @DesAirport =STUFF (@DesAirport,1 , CHARINDEX(',',@DesAirport),'')
END
END
ELSE
INSERT INTO @CityCode(cityCode) Values(@DesAirport)
select * from @CityCode
DECLARE @DepAirport nvarchar(200)
DECLARE @DesAirport nvarchar(200)set @DepAirport ='UA,NW,CX,CO'
set @DesAirport ='UA,NW,CX,CO,DL,AA'DECLARE @CityCode table( cityCode nvarchar(10) collate Latin1_General_BIN)IF len(@DepAirport)>3
BEGIN
SET @DepAirport = ISNULL( @DepAirport,'')+ ','
WHILE CHARINDEX(',',@DepAirport) >0
BEGIN
INSERT INTO @CityCode(cityCode) Values(LEFT (@DepAirport ,CHARINDEX(',',@DepAirport)-1))
SET @DepAirport =STUFF (@DepAirport,1 , CHARINDEX(',',@DepAirport),'')
END
END
ELSE
INSERT INTO @CityCode(cityCode) Values(@DepAirport)IF len(@DesAirport)>3
BEGIN
SET @DesAirport = ISNULL( @DesAirport,'')+ ','
WHILE CHARINDEX(',',@DesAirport) >0
BEGIN
INSERT INTO @CityCode(cityCode) Values(LEFT (@DesAirport ,CHARINDEX(',',@DesAirport)-1))
SET @DesAirport =STUFF (@DesAirport,1 , CHARINDEX(',',@DesAirport),'')
END
END
ELSE
INSERT INTO @CityCode(cityCode) Values(@DesAirport)
select * from @CityCode
改为动态,05用xml分拆
DECLARE @DesAirport nvarchar(200) set @DepAirport ='UA,NW,CX,CO'
set @DesAirport ='UA,NW,CX,CO,DL,AA' SET @DepAirport='SELECT cityCode='''+REPLACE(@DepAirport,',',''' UNION ALL SELECT ''')
SET @DesAirport='SELECT '''+REPLACE(@DesAirport,',',''' UNION ALL SELECT ''')EXEC ('SELECT * FROM ('+@DepAirport+''' UNION ALL '+@DesAirport+''') AS t')/*
cityCode
--------
UA
NW
CX
CO
UA
NW
CX
CO
DL
AA(10 行受影响)
*/
DECLARE @DepAirport nvarchar(200)
DECLARE @DesAirport nvarchar(200) set @DepAirport ='UA,NW,CX,CO'
set @DesAirport ='UA,NW,CX,CO,DL,AA' DECLARE @CityCode table( cityCode nvarchar(10) collate Latin1_General_BIN) select @DepAirport='select '''+replace(@DepAirport,',',''' union all select ''')+'''',
@DesAirport='select '''+replace(@DesAirport,',',''' union all select ''')+''''insert @CityCode exec(@DepAirport)
insert @CityCode exec(@DesAirport)select * from @CityCode
(4 行受影响)(6 行受影响)
cityCode
----------
UA
NW
CX
CO
UA
NW
CX
CO
DL
AA(10 行受影响)
DECLARE @ids VARCHAR(100)
SELECT @ids='5,2,3,4'DECLARE @t TABLE(nid INT IDENTITY(1,1),id VARCHAR(10))INSERT @t (id) SELECT CAST(b.v AS VARCHAR(10))
FROM
(
SELECT x=CAST('<r>' + REPLACE(@ids,',','</r><r>') + '</r>' AS XML)
) a
CROSS APPLY
(SELECT v=x.query('./text()') FROM a.x.nodes('//r') AS t(x)) b
如何插入到@CityCode 这个临时表中啊,谢谢了DECLARE @CityCode table( cityCode nvarchar(10) collate Latin1_General_BIN)
DECLARE @ids VARCHAR(100)
SELECT @ids='5,2,3,4'SELECT b.*
FROM
(
SELECT x=CAST('<r>' + REPLACE(@ids,',','</r><r>') + '</r>' AS XML)
) a
CROSS APPLY
(SELECT v=x.query('./text()') FROM a.x.nodes('//r') AS t(x)) b
DECLARE @DepAirport nvarchar(200)
DECLARE @DesAirport nvarchar(200) set @DepAirport ='UA,NW,CX,CO'
set @DesAirport ='UA,NW,CX,CO,DL,AA' DECLARE @CityCode table( cityCode nvarchar(10) collate Latin1_General_BIN) SELECT TOP 100 IDENTITY(INT,1,1) as N INTO #tmp FROM sysobjects
INSERT INTO @cityCodeSELECT SUBSTRING(s,n,CHARINDEX(',',s+',',n+1)-n)
FROM (
SELECT @DepAirport s UNION ALL SELECT @DesAirport
) a,#tmp b
WHERE SUBSTRING(','+s,N,1)=','select * from @CityCode DROP TABLE #tmp