34,838
社区成员




--sql server 2000使用临时表解决
create table tb(FUserID int, FGroupID int, FMID varchar(20))
insert into tb values(1, 1, '2,3,4')
insert into tb values(1, 2, '5,6,7')
insert into tb values(1, 3, '290,34,434')
insert into tb values(2, 1, '4,443,222')
insert into tb values(2, 2, '43,32,234')
go
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO tmp FROM syscolumns a, syscolumns b
SELECT fmid = SUBSTRING(A.[fmid], B.id, CHARINDEX(',', A.[fmid] + ',', B.id) - B.id) , A.FgroupID
FROM tb A, tmp B
WHERE SUBSTRING(',' + A.[fmid], B.id, 1) = ',' and A.FuserID = 1
drop table tb,tmp
/*
fmid FgroupID
-------------------- -----------
2 1
3 1
4 1
5 2
6 2
7 2
290 3
34 3
434 3
(所影响的行数为 9 行)
*/
--如果fmid最多不超过四个值,可使用PARSENAME.(本例只有三个,如果有四个,加上parsename(replace(fmid , ',' , '.'),4))
create table tb(FUserID int, FGroupID int, FMID varchar(20))
insert into tb values(1, 1, '2,3,4')
insert into tb values(1, 2, '5,6,7')
insert into tb values(1, 3, '290,34,434')
insert into tb values(2, 1, '4,443,222')
insert into tb values(2, 2, '43,32,234')
go
select fmid = parsename(replace(fmid , ',' , '.'),3) , fgroupid from tb where fuserid = 1
union all
select fmid = parsename(replace(fmid , ',' , '.'),2) , fgroupid from tb where fuserid = 1
union all
select fmid = parsename(replace(fmid , ',' , '.'),1) , fgroupid from tb where fuserid = 1
order by fgroupid , fmid
drop table tb
/*
fmid FgroupID
-------------------- -----------
2 1
3 1
4 1
5 2
6 2
7 2
290 3
34 3
434 3
(所影响的行数为 9 行)
*/
--sql server 2005使用OUTER APPLY解决.
create table tb(FUserID int, FGroupID int, FMID varchar(20))
insert into tb values(1, 1, '2,3,4')
insert into tb values(1, 2, '5,6,7')
insert into tb values(1, 3, '290,34,434')
insert into tb values(2, 1, '4,443,222')
insert into tb values(2, 2, '43,32,234')
go
SELECT B.FMID , A.FGroupID
FROM(
SELECT FUserID , FGroupID , FMID = CONVERT(xml,'<root><v>' + REPLACE([FMID], ',', '</v><v>') + '</v></root>') FROM tb where FUserID = 1
)A
OUTER APPLY(
SELECT FMID = N.v.value('.', 'varchar(100)') FROM A.[FMID].nodes('/root/v') N(v)
)B
drop table tb
/*
FMID FGroupID
---------------------------------------------------------------------------------------------------- -----------
2 1
3 1
4 1
5 2
6 2
7 2
290 3
34 3
434 3
(9 行受影响)
*/
declare @t table(FUserID int,FGroupID int,FMID varchar(10))
insert @t select
1 , 1 , '2,3,4' union all select
1 , 2 , '5,6,7' union all select
1 , 3 , '290,34,434' union all select
2 , 1 , '4,443,222' union all select
2 , 2, '43,32,234'
--需要在sql server 2005运行
--一条语句也可以完成你的功能
select fmid,fgroupid
from (select fgroupid
,parsename(replace(fmid,',','.'),3) as fmid1
,parsename(replace(fmid,',','.'),2) as fmid2
,parsename(replace(fmid,',','.'),1) as fmid3
from @t where fuserid =1 ) as f
unpivot
(fmid for fmid_name
in(fmid1,fmid2,fmid3) )as unpvt
order by fgroupid
declare @t table(FUserID int,FGroupID int,FMID varchar(10))
insert @t select
1 , 1 , '2,3,4' union all select
1 , 2 , '5,6,7' union all select
1 , 3 , '290,34,434' union all select
2 , 1 , '4,443,222' union all select
2 , 2, '43,32,234'
--需要在sql server 2005运行
--分析fmid,并将userid=1的数据取出来,这里是公用表达式
;with Split
as (select FGroupID
,parsename(replace(fmid,',','.'),3) as fmid1
,parsename(replace(fmid,',','.'),2) as fmid2
,parsename(replace(fmid,',','.'),1) as fmid3
from @t
where fuserid = 1 )
--通过行列转换,可以完成你的功能
select fmid,fgroupid
from (select fgroupid,fmid1
,fmid2,fmid3 from Split) as f
unpivot
(fmid for fmid_name
in(fmid1,fmid2,fmid3) )as unpvt
order by fgroupid
declare @t table(FUserID int,FGroupID int,FMID varchar(10))
insert @t select
1 , 1 , '2,3,4' union all select
1 , 2 , '5,6,7' union all select
1 , 3 , '290,34,434' union all select
2 , 1 , '4,443,222' union all select
2 , 2, '43,32,234'
select FGroupID,PARSENAME(replace(fmid,',','.'),3) as fmid from @t union all
select FGroupID,PARSENAME(replace(fmid,',','.'),2) from @t union all
select FGroupID,PARSENAME(replace(fmid,',','.'),1) from @t
order by FGroupID
declare @t table(FUserID int,FGroupID int,FMID varchar(10))
insert @t select
1 , 1 , '2,3,4' union all select
1 , 2 , '5,6,7' union all select
1 , 3 , '290,34,434' union all select
2 , 1 , '4,443,222' union all select
2 , 2, '43,32,234'
select FGroupID,PARSENAME(replace(fmid,',','.'),3) as fmid from @t union all
select FGroupID,PARSENAME(replace(fmid,',','.'),2) from @t union all
select FGroupID,PARSENAME(replace(fmid,',','.'),1) from @t
order by FGroupID
/*
FGroupID fmid
----------- --------------------------------------------------------------------------------------------------------------------------------
1 2
1 4
1 3
1 443
1 4
1 222
2 234
2 7
2 32
2 43
2 6
2 5
3 290
3 34
3 434
(所影响的行数为 15 行)
*/
create table #t
(
FGroupID int,
FMID varchar(60)
)
insert #t select 1, '2,3,4'
insert #t select 2 , '5,6,7'
insert #t select 3 , '290,34,434'
insert #t select 1 , '4,443,222'
insert #t select 2 , '43,32,234'
go
declare @sql varchar(max)
set @sql = ' select '
select @sql = @sql + replace(FMID,',',','+ltrim(FGroupID)+' union all select ') + ',' + ltrim(FGroupID) + ' union all select ' from #t
set @sql = substring(@sql,1,len(@sql)-17)
exec (@sql)