sql 如何寫存儲過程
serialno username 編號 主管 時間
BJ-ASH-00272王凱154714劉桂玲3/8/2007
BJ-ASH-00275警衛室1154732劉桂玲3/8/2007
BJ-ASH-00275警衛室2154732劉桂玲3/8/2007
BJ-ASH-00311方廷燈159929馬陽3/15/2007BJ-ASH-00312警衛室1159980馬陽3/15/2007BJ-ASH-00312警衛室2159980馬陽3/15/2007
现想做成, 如果serialno相同, 则把username合并起来,其它栏位是一样的,
请教如何实现, 写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 F
SQL文
结果
列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 tbltest
go
--方法2(不用函数实现更新、查询)
--如下用于几列合并一列方法1比方法2效率高
declare @tb table(列A int,列B int,列C varchar(50),con int identity(1,1))
insert @tb
select * from ta
begin 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)
end
select 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)
go
if 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 tb
drop 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
go
create 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)
go
if 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 test
drop 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
go
create 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, ' ')
end
select distinct serialno=dbo.test_f(serialno,username,編號,主管,時間),serialno,username,編號,主管,時間
from ta
serialno 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 行受影响)
