有一张表m
jid no fid mnumber
1 1001 0 0
0 1001 0 0
1 1002 0 0
0 1002 0 0
1 1003 5 m101
0 1003 5 m101
1 1003 65 m102
0 1003 81 m102
1 1004 89 m105
0 1004 89 m105
1 1005 91 m106
想得到的结果是:
no fid fnumber
1001 0,0 0
1002 0,0 0
1003 5,5 m101
1003 65,81 m102
1004 89,89 m105
1005 91 m106解释:
1. jid表示该表只有两种业务,要么是1,要么是0,现在的目的就是要把这两种业务中,no相同并且mnumber相同的fid算出来,
组成a,b的形式.如果只有一种业务,比如jid=1,那么fid自然就是独立的,比如上例只有91.
2.昨天已经发了一贴,见http://topic.csdn.net/u/20100217/17/48577602-8bb9-4a3a-8eb6-592bc9c11c63.html
后来发现我提的问题是有问题的,我并没有考虑到还必须带上另一个条件mnumber,这里想写得更清楚一点,希望大家帮我写出这个语句。谢谢。
3.祝大家新春节愉快。
jid no fid mnumber
1 1001 0 0
0 1001 0 0
1 1002 0 0
0 1002 0 0
1 1003 5 m101
0 1003 5 m101
1 1003 65 m102
0 1003 81 m102
1 1004 89 m105
0 1004 89 m105
1 1005 91 m106
想得到的结果是:
no fid fnumber
1001 0,0 0
1002 0,0 0
1003 5,5 m101
1003 65,81 m102
1004 89,89 m105
1005 91 m106解释:
1. jid表示该表只有两种业务,要么是1,要么是0,现在的目的就是要把这两种业务中,no相同并且mnumber相同的fid算出来,
组成a,b的形式.如果只有一种业务,比如jid=1,那么fid自然就是独立的,比如上例只有91.
2.昨天已经发了一贴,见http://topic.csdn.net/u/20100217/17/48577602-8bb9-4a3a-8eb6-592bc9c11c63.html
后来发现我提的问题是有问题的,我并没有考虑到还必须带上另一个条件mnumber,这里想写得更清楚一点,希望大家帮我写出这个语句。谢谢。
3.祝大家新春节愉快。
--> Author : .
--> Date : 2010-02-18 08:53:00
if not object_id('tb') is null
drop table tb
Go
Create table tb([jid] int,[no] int,[fid] int,[mnumber] nvarchar(4))
Insert tb
select 1,1001,0,N'0' union all
select 0,1001,0,N'0' union all
select 1,1002,0,N'0' union all
select 0,1002,0,N'0' union all
select 1,1003,5,N'm101' union all
select 0,1003,5,N'm101' union all
select 1,1003,65,N'm102' union all
select 0,1003,81,N'm102' union all
select 1,1004,89,N'm105' union all
select 0,1004,89,N'm105' union all
select 1,1005,91,N'm106'
Go
Select [NO],
fid=stuff((select ','+ltrim(fid) from tb where no=t.no and mnumber=t.mnumber for xml path('')),1,1,'')
from tb t
group by [no],mnumber
/*
NO fid
------------------
1001 0,0
1002 0,0
1003 5,5
1003 65,81
1004 89,89
1005 91(6 個資料列受到影響)
*/
STUFF这个函数在2000里面没有。
returns varchar(50)
as
begin
declare @s varchar(800)
select @s=isnull(@s+',','')+ltrim(fid)
from m
where no=@no and fnumber=@fnumber
return @s
end
goselect
no,
fid=dbo.f_str(no,fnumber),
fnumber
from m
group by no,fnumber
drop table m
Go
Create table m([jid] int,[no] int,[fid] int,[fnumber] nvarchar(4))
Insert m
select 1,1001,0,N'0' union all
select 0,1001,0,N'0' union all
select 1,1002,0,N'0' union all
select 0,1002,0,N'0' union all
select 1,1003,5,N'm101' union all
select 0,1003,5,N'm101' union all
select 1,1003,65,N'm102' union all
select 0,1003,81,N'm102' union all
select 1,1004,89,N'm105' union all
select 0,1004,89,N'm105' union all
select 1,1005,91,N'm106'
Gocreate function f_str(@no int,@fnumber varchar(20))
returns varchar(50)
as
begin
declare @s varchar(800)
select @s=isnull(@s+',','')+ltrim(fid)
from m
where no=@no and fnumber=@fnumber
return @s
end
goselect
no,
fid=dbo.f_str(no,fnumber),
fnumber
from m
group by no,fnumber /**
no fid fnumber
----------- -------------------------------------------------- -------
1001 0,0 0
1002 0,0 0
1003 5,5 m101
1003 65,81 m102
1004 89,89 m105
1005 91 m106(所影响的行数为 6 行)
**/