导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

求一汇总语句

luckljtchinaren 2007-12-11 11:52:26
汇总员工的上下级。
源表:
[备注]Name为员工姓名,RName为上下级姓名,Type表示上下级关系:10为上级,20为下级。
Name RName Type
A A1 10
A A2 10
A A3 20
A A4 20
A A5 20
B B1 10
B B2 20
B B3 20
B B4 20
C C1 20
C C2 20
D D1 10
D D2 10

查询要求1.按员工进行汇总上下级,所有上级为一列,上级之间以顿号分开;所有下级为一列,下级之间以顿号分开。结果形式如下:
Name UpNames DownNames
A   A1、A2 A3、A4
B B1 B2、B3、B4
C .... ....
D .... ....

查询要求2.按员工进行汇总上下级,每一上级或下级为一列(假设最多有三个上级、最多有三个下级)。结果形式如下:
Name Up1 Up2 Up3 Down1 Down2 Down3
A   A1  A2 NULL A3  A4   A5
B    。。。。。。。。。。。。。。。。。

麻烦各位高人了,谢谢:)
...全文
68 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
pt1314917 2007-12-11



加索引试试。
函数已经无法优化了,,顶多根据实际需求,把函数返回值大小改小一点。现在是1000,如果楼主数据没那么多,
可以改成100或其他。
主要还是加索引
create function sdfs(@name varchar(10),@type int)
returns varchar(100)
as
begin
declare @sql varchar(100)
select @sql=isnull(@sql+',','')+rname from tb where name=@name and type=@type
return @sql
end

回复
luckljtchinaren 2007-12-11
果然是高手如云阿!
问题已经根据答复解决了,非常感谢!
让又多了用自定义函数的思路,以前虽然了解但还没有自已创建过自定义函数。
不过调用函数,循环执行自定义函数的select语句,使用得效率相对慢了点,我本地查询分析器中执行要十几二十秒。
不知道有没有更快的语句,因为要放到程序中的话,十几二十秒可能会出现超时异常。
再次谢谢大家的帮助:)
Best regards
lucky
回复
dawugui 2007-12-11
create table tb(Name varchar(10),RName varchar(10),Type int)
insert into tb values('A', 'A1', 10 )
insert into tb values('A', 'A2', 10 )
insert into tb values('A', 'A3', 20 )
insert into tb values('A', 'A4', 20 )
insert into tb values('A', 'A5', 20 )
insert into tb values('B', 'B1', 10 )
insert into tb values('B', 'B2', 20 )
insert into tb values('B', 'B3', 20 )
insert into tb values('B', 'B4', 20 )
insert into tb values('C', 'C1', 20 )
insert into tb values('C', 'C2', 20 )
insert into tb values('D', 'D1', 10 )
insert into tb values('D', 'D2', 10 )
go
--创建一个合并的函数
create function f_hb(@name varchar , @type int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(RName as varchar) from tb where name = @name and type = @type
set @str = right(@str , len(@str))
return(@str)
End
go

--调用自定义函数得到结果:
select name ,
upnames = case when left(upnames,1) = ',' and len(upnames)>0 then right(upnames,len(upnames)-1) else '' end ,
DownNames = case when left(DownNames,1) = ',' and len(DownNames)>0 then right(DownNames,len(DownNames)-1) else '' end
from
(
select distinct name ,dbo.f_hb(name,10) as UpNames , dbo.f_hb(name,20) as DownNames from tb
) t

drop table tb
drop function f_hb

/*
name upnames DownNames
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A A1,A2 A3,A4,A5
B B1 B2,B3,B4
C C1,C2
D D1,D2

(所影响的行数为 4 行)

*/
回复
pt1314917 2007-12-11

1\create table tb(name varchar(10),rname varchar(10),type int)
insert into tb select 'A','A1',10
insert into tb select 'A','A2',10
insert into tb select 'A','A3',20
insert into tb select 'A','A4',20
insert into tb select 'A','A5',20
insert into tb select 'B','B1',10
insert into tb select 'B','B2',20
insert into tb select 'B','B3',20
insert into tb select 'B','B4',20
insert into tb select 'C','C1',20
insert into tb select 'C','C2',20
insert into tb select 'D','D1',10
insert into tb select 'D','D2',10

create function sdfs(@name varchar(10),@type int)
returns varchar(1000)
as
begin
declare @sql varchar(1000)
select @sql=isnull(@sql+',','')+rname from tb where name=@name and type=@type
return @sql
end

select distinct name,dbo.sdfs(name,10),dbo.sdfs(name,20) from tb
回复
dawugui 2007-12-11
/*
带符号合并行列转换(2007-11-19于海南三亚)

有表tb,其数据如下:
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

--创建一个合并的函数
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
drop function f_hb

/*
结果
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)
*/

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

--创建一个合并的函数
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(OP as varchar) + '('
+ cast(sc as varchar) + ')'
from tb 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 tb

drop table tb
drop function f_hb

/*
结果
id pr con ops
---------- ---------- ---------- ------------------
001 p c 差(6),好(2),一般(4)
002 w e 差(8),好(7),一般(1)

(所影响的行数为 2 行)
*/

----------------------------------------------------
/*如何将一列中所有的值一行显示
数据源
a
b
c
d
e
结果
a,b,c,d,e
*/

create table tb(col varchar(20))
insert tb values ('a')
insert tb values ('b')
insert tb values ('c')
insert tb values ('d')
insert tb values ('e')
go

--方法一
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + t.col + ',' from (select col from tb) as t
set @sql='select result = ''' + @sql + ''''
exec(@sql)
/*
result
----------
a,b,c,d,e,
*/

--方法二
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + col from tb
print @output
/*
a,b,c,d,e
*/

drop table tb

回复
fcuandy 2007-12-11
下面的写法,会弄的你系统当机.hehe

create table tb(name varchar(10),rname varchar(10),type int)
insert into tb select 'A','A1',10
insert into tb select 'A','A2',10
insert into tb select 'A','A3',20
insert into tb select 'A','A4',20
insert into tb select 'A','A5',20
insert into tb select 'B','B1',10
insert into tb select 'B','B2',20
insert into tb select 'B','B3',20
insert into tb select 'B','B4',20
insert into tb select 'C','C1',20
insert into tb select 'C','C2',20
insert into tb select 'D','D1',10
insert into tb select 'D','D2',10
DECLARE @x XML
SET @x=(SELECT * FROM tb FOR XML PATH,TYPE)
SELECT DISTINCT CAST(v.query('./name/text()') AS NVARCHAR(20)),
CASE WHEN CAST(v.query('./name/text()') AS NVARCHAR(20))='A' THEN CAST(v.query('//row[name=''A'']/rname/text()') AS NVARCHAR(100))
WHEN CAST(v.query('./name/text()') AS NVARCHAR(20))='B' THEN CAST(v.query('//row[name=''B'']/rname/text()') AS NVARCHAR(100))
WHEN CAST(v.query('./name/text()') AS NVARCHAR(20))='C' THEN CAST(v.query('//row[name=''C'']/rname/text()') AS NVARCHAR(100))
WHEN CAST(v.query('./name/text()') AS NVARCHAR(20))='D' THEN CAST(v.query('//row[name=''D'']/rname/text()') AS NVARCHAR(100))
END
FROM @x.nodes('//row') AS t(v)

drop table tb
回复
-狙击手- 2007-12-11
create table ta(Name varchar(2),RName varchar(3),Type int)
insert ta select 'A','A1',10
insert ta select 'A','A2',10
insert ta select 'A','A3',20
insert ta select 'A','A4',20
insert ta select 'A','A5',20
insert ta select 'B','B1',10
insert ta select 'B','B2',20
insert ta select 'B','B3',20
insert ta select 'B','B4',20
insert ta select 'C','C1',20
insert ta select 'C','C2',20
insert ta select 'D','D1',10
insert ta select 'D','D2',10


go

--创建用户定义函数,每个子节点de父节点的信息
create function f_getParent(@ID varchar(4),@type int)
returns varchar(40)
as
begin
declare @ret varchar(40)


select @ret=','+rtrim(rname)+isnull(@ret,'')
from
ta a
where
a.Name=@ID and Type=@type

set @ret=stuff(@ret,1,1,'')
return @ret
end
go

--执行查询
select distinct name,isnull(dbo.f_getParent(name,10),'') as parentname ,
isnull(dbo.f_getParent(name,20),'') as childname
from ta
go

--输出结果
/*
name parentname childname
---- ---------------------------------------- ----------------------------------------
A A2,A1 A5,A4,A3
B B1 B4,B3,B2
C C2,C1
D D2,D1

(所影响的行数为 4 行)
*/

--删除测试数据
drop function f_getParent
drop table ta
go
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告