全部
基础类
应用实例
新技术前沿

求一SQL语句

kugoo2338903 2008-03-30 06:32:02

FUserID FGroupID FMID
1 1 2,3,4
1 2 5,6,7
1 3 290,34,434
2 1 4,443,222
2 2 43,32,234

比如我查FUserID为1的记录,如何得到这样的效果:
FMID FGroupID
2 1
3 1
4 1
5 2
6 2
7 2
290 3
34 3
434 3

...全文

140 点赞收藏 21

写回复

21 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

dawugui 2008-03-31

--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 行受影响)

*/

回复赞

yyyyzzzz_2002 2008-03-31

老乌龟的 OUTER APPLY解决用的很好,并且利用了xml.sql server2005可以处理很多复杂的东西,不错.

回复赞

剑心永远OK 2008-03-31

学习了。。

回复赞

LutzMark 2008-03-31

学习

回复赞

kugoo2338903 2008-03-31

用潇洒老乌龟的实现了,呵呵,各位辛苦了.
其实结贴分配分也是很辛苦的

回复赞

kugoo2338903 2008-03-31

to 我很丑，但我不温柔
不好意思,你的语句我没看懂

回复赞

kugoo2338903 2008-03-31

要不就弄个简单点的也行,单行记录,然后再把FGroupID字段去掉
FUserID FMID
1 2,3,4,555

2 4,443,222,343,4543

查FUserID为1条件时,结果是:
1 2
1 3
1 4
1 555

回复赞

kugoo2338903 2008-03-31

to 无枪狙击手
因为我的FMid字段内容是不确定的,可能有0个,也可能有N个,我仿照你的写法,发现内容一多,数据就不对了

回复赞

utpcb 2008-03-31

行列转换

回复赞

yyyyzzzz_2002 2008-03-30

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

回复赞

kugoo2338903 2008-03-30

多谢各位,我先试试看,明天结帖

回复赞

yyyyzzzz_2002 2008-03-30

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

回复赞

pt1314917 2008-03-30



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

回复赞

想飞的狼 2008-03-30

学习了
PARSENAME
返回对象名的指定部分。可以检索的对象部分有对象名、所有者名称、数据库名称和服务器名称。

说明 PARSENAME 函数不表明所指定名称的对象是否存在，而只是返回给定对象名的指定部分。

语法
PARSENAME ( 'object_name' , object_piece )

参数
'object_name'

要检索其指定部分的对象名。object_name 是 sysname 值。本参数是可选的合法对象名。如果该对象名的所有部分均符合要求，则该名称由以下四部分组成：服务器名称、数据库名称、所有者名称和对象名。

object_piece

要返回的对象部分。object_piece 是 int 值，可以为下列值。

Value 描述
1 对象名
2 所有者名称
3 数据库名称
4 服务器名称

返回类型
nchar

注释
如果符合下列条件之一，则 PARSENAME 返回 NULL 值：

object_name 或 object_piece 为 NULL 值。

发生语法错误。

所请求的对象部分长度为 0，并且是无效的 Microsoft® SQL Server™ 标识符。零长度的对象名将导致整个合法名称无效。
示例
本示例使用 PARSENAME 返回有关 pubs 数据库中 authors 表的信息。

USE pubs
SELECT PARSENAME('pubs..authors', 1) AS 'Object Name'
SELECT PARSENAME('pubs..authors', 2) AS 'Owner Name'
SELECT PARSENAME('pubs..authors', 3) AS 'Database Name'
SELECT PARSENAME('pubs..authors', 4) AS 'Server Name'

下面是结果集：

Object Name
------------------------------
authors

(1 row(s) affected)

Owner Name
------------------------------
(null)

(1 row(s) affected)

Database Name
------------------------------
pubs

(1 row(s) affected)

Server Name
------------------------------
(null)

(1 row(s) affected)

回复赞

hery2002 2008-03-30

关注,一条SQL好像有难度.

回复赞

viva369 2008-03-30

PARSENAME还有这个函数，学习了 ^_^

回复赞

-狙击手- 2008-03-30

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 行）



*/

回复赞

viva369 2008-03-30



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)