sql 如何寫存儲過程

又是列聚合。参考：/*
表 tbltest数据如下：列A  列B  列B
1     1    A
1     1    B
1     1    C
1     2    F
1     2    G
2     1    E
2     1    F
2     2    FSQL文结果列A  列B  列B
1     1     A，B，C
1     2     F，G
2     1     E，F
2     2     F

*/create table tbltest(列A int, 列B int,列C varchar(100))
go
insert into tbltest
select 1,1,'A' union all
select 1,1,'B' union all
select 1,1,'C' union all
select 1,2,'F' union all
select 1,2,'G' union all
select 2,1,'E' union all
select 2,1,'F' union all
select 2,2,'F'
go
--写一个聚合函数：
create function dbo.fn_Merge(@F1 int,@F2 int)
returns varchar(8000)
as
begin
   declare @r varchar(8000)
   set @r=''
   select @r=@r+','+列C from tbltest where 列A=@F1 and 列B=@F2
   return stuff(@r,1,1,'')
end
go-- 调用函数
select 列A,列B, dbo.fn_Merge(列A,列B) as 列C from tbltest group by 列A,列B

go
drop table tbltestgo
--方法2(不用函数实现更新、查询)
--如下用于几列合并一列方法1比方法2效率高
declare @tb table(列A int,列B int,列C varchar(50),con int identity(1,1))
insert @tb
select * from tabegin tran
while exists(select 1 from @tb)
begin
update a
set a.列C=a.列C+','+b.列C
from ta a ,@tb b
where a.列A=b.列A  and a.列B=b.列B and
not exists(select * from @tb where 列A=b.列A and 列B=b.列B  and con<b.con )delete b
from @tb b where not exists(select 1 from @tb where 列A=b.列A and 列B=b.列B and con<b.con)
endselect distinct 列A,列B, [列B显示]=stuff(列C,1,charindex(',',列C),'') from ta
所影响的行数为 1 行）列A          列B          列B显示
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           1           A,B,C
1           2           F,G
2           1           E,F
2           2           F（所影响的行数为 4 行）
drop function fn_Merge

--带符号合并行列转换--有表t，其数据如下：
  a b
  1 1
  1 2
  1 3
  2 1
  2 2
  3 1
--如何转换成如下结果:
  a b
  1 1,2,3
  2 1,2
  3 1 create table tb
(
   a int,
   b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
goif object_id('pubs..f_hb') is not null
   drop function f_hb
go--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(b as varchar) from tb where a = @a
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果
a           b
----------- ------
1           1,2,3
2           1,2
3           1（所影响的行数为 3 行）
多个前列的合并
数据的原始状态如下：
ID  PR   CON  OP    SC
001 p    c    差    6
001 p    c    好    2
001 p    c    一般  4
002 w    e    差    8
002 w    e    好    7
002 w    e    一般  1
===========================
用SQL语句实现，变成如下的数据
ID  PR   CON  OPS
001 p    c    差(6),好(2),一般(4)
002 w    e    差(8),好(7),一般(1)if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb
(
id varchar(10),
pr varchar(10),
con varchar(10),
op varchar(10),
sc int
)

insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '差',    6)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '好',    2)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '一般',  4)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '差',    8)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '好',    7)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '一般',  1)
goif object_id('pubs..test') is not null
   drop table test
go
select ID,PR,CON , OPS = op + '(' + cast(sc as varchar(10)) + ')' into test from tb--创建一个合并的函数
if object_id('pubs..f_hb') is not null
   drop function f_hb
go
create function f_hb(@id varchar(10),@pr varchar(10),@con varchar(10))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(OPS as varchar) from test where id = @id and @pr = pr and @con = con
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct id ,pr , con , dbo.f_hb(id,pr,con) as OPS from testdrop table tb
drop table test--结果
id         pr         con        OPS
---------- ---------- ---------- -------------------
001        p          c          差(6),好(2),一般(4)
002        w          e          差(8),好(7),一般(1)（所影响的行数为 2 行）create table b
(col varchar(20))insert b values ('a')
insert b values ('b')
insert b values ('c')
insert b values ('d')
insert b values ('e')
declare @sql varchar(1024)
set @sql=''
select @sql=@sql+b.col+',' from (select col from b) as b
set @sql='select '''+@sql+''''
exec(@sql)

So long i think.
I prefer the simple one..

if object_id('pubs..tb') is not null
   drop table tb
gocreate table tb(serialno varchar(20),username varchar(10),編號 varchar(10),主管 varchar(10),時間 varchar(10))
insert into tb(serialno,username,編號,主管,時間) values('BJ-ASH-00272','王凱'   ,'154714','劉桂玲','3/8/2007')
insert into tb(serialno,username,編號,主管,時間) values('BJ-ASH-00275','警衛室1','154732','劉桂玲','3/8/2007')
insert into tb(serialno,username,編號,主管,時間) values('BJ-ASH-00275','警衛室2','154732','劉桂玲','3/8/2007')
insert into tb(serialno,username,編號,主管,時間) values('BJ-ASH-00311','方廷燈' ,'159929','馬陽','3/15/2007')
insert into tb(serialno,username,編號,主管,時間) values('BJ-ASH-00312','警衛室1','159980','馬陽','3/15/2007')
insert into tb(serialno,username,編號,主管,時間) values('BJ-ASH-00312','警衛室2','159980','馬陽','3/15/2007')
go
if object_id('pubs..f_hb') is not null
   drop function f_hb
go--创建一个合并的函数
create function f_hb(@serialno varchar(20))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(username as varchar) from tb where serialno = @serialno
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select m.*,n.name from
(select distinct serialno,編號,主管,時間 from tb) m,
(select distinct serialno ,dbo.f_hb(serialno) as name from tb) n
where m.serialno = n.serialno
drop table tb/*
serialno             編號       主管        時間       name
-------------------- ---------- ---------- ---------- ---------------
BJ-ASH-00272         154714     劉桂玲      3/8/2007   王凱
BJ-ASH-00275         154732     劉桂玲      3/8/2007   警衛室1,警衛室2
BJ-ASH-00311         159929     馬陽        3/15/2007  方廷燈
BJ-ASH-00312         159980     馬陽        3/15/2007  警衛室1,警衛室2（所影响的行数为 4 行）
*/

create table ta(serialno varchar(20), username varchar(10),   編號  int,   主管 varchar(10),    時間 datetime)
insert ta select 'BJ-ASH-00272', '王凱', 154714, '劉桂玲', '3/8/2007'
union all select 'BJ-ASH-00275', '警衛室1', 154732, '劉桂玲', '3/8/2007'
union all select 'BJ-ASH-00275', '警衛室2', 154732, '劉桂玲', '3/8/2007'
union all select 'BJ-ASH-00311', '方廷燈', 159929, '馬陽', '3/15/2007'
union all select 'BJ-ASH-00312', '警衛室1', 159980, '馬陽', '3/15/2007'
union all select 'BJ-ASH-00312', '警衛室2', 159980, '馬陽', '3/15/2007'
create function test_f(@serialno varchar(20),@username varchar(10),@編號  int,@主管 varchar(10), @時間 datetime)
returns varchar(4000)
as
begin
declare @s varchar(4000)
set @s=''
if (select count(1) from ta where serialno=@serialno and 編號=@編號 and 主管=@主管 and 時間=@時間
group by binary_checksum(serialno,編號,主管,時間))>1
begin
select @s=@s+'/'+username from ta
where  serialno=@serialno and 編號=@編號 and 主管=@主管 and 時間=@時間
end
else
begin
select @s=@s+'/'+serialno from ta
where  username=@username and 編號=@編號 and 主管=@主管 and 時間=@時間
end
return stuff(@s,1,1,'')
endselect distinct serialno=dbo.test_f(serialno,username,編號,主管,時間),serialno,username,編號,主管,時間
from taserialno                                                                                                                                                                                                                                                         serialno             username   編號          主管         時間
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- ---------- ----------- ---------- -----------------------
BJ-ASH-00272                                                                                                                                                                                                                                                     BJ-ASH-00272         王凱         154714      劉桂玲        2007-03-08 00:00:00.000
BJ-ASH-00311                                                                                                                                                                                                                                                     BJ-ASH-00311         方廷燈        159929      馬陽         2007-03-15 00:00:00.000
警衛室1/警衛室2                                                                                                                                                                                                                                                        BJ-ASH-00275         警衛室1       154732      劉桂玲        2007-03-08 00:00:00.000
警衛室1/警衛室2                                                                                                                                                                                                                                                        BJ-ASH-00275         警衛室2       154732      劉桂玲        2007-03-08 00:00:00.000
警衛室1/警衛室2                                                                                                                                                                                                                                                        BJ-ASH-00312         警衛室1       159980      馬陽         2007-03-15 00:00:00.000
警衛室1/警衛室2                                                                                                                                                                                                                                                        BJ-ASH-00312         警衛室2       159980      馬陽         2007-03-15 00:00:00.000(6 行受影响)

select distinct [合并值]=dbo.test_f(serialno,username,編號,主管,時間)
from ta
合并值
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BJ-ASH-00272
BJ-ASH-00311
警衛室1/警衛室2(3 行受影响)

以上为serialno相同进合并username,其它合并serialno.
以下为不加判断合并usernamecreate function test_f(@serialno varchar(20),@username varchar(10),@編號 int,@主管 varchar(10), @時間 datetime)
returns varchar(4000)
as
begin
declare @s varchar(4000)
set @s=''
select @s=@s+'/'+username from ta
where serialno=@serialno and 編號=@編號 and 主管=@主管 and 時間=@時間
return stuff(@s,1,1,'')
endselect distinct [合并值]=dbo.test_f(serialno,username,編號,主管,時間)
from ta

serialno        username    編號     主管     時間
BJ-ASH-00272      王凱       154714   劉桂玲   3/8/2007
BJ-ASH-00275      警衛室1    154732   劉桂玲   3/8/2007
BJ-ASH-00275      警衛室2    154732   劉桂玲   3/8/2007
BJ-ASH-00311      方廷燈     159929   馬陽     3/15/2007
BJ-ASH-00312      警衛室1    159980   馬陽     3/15/2007
BJ-ASH-00312      警衛室2    159980   馬陽     3/15/2007

调试易

sql 如何寫存儲過程

解决方案 »