求一汇总语句

2007-12-11 11:52:26 160 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

luckljtchinaren 墨衍会员 · AI 创作全网分发关注 + 加入社区

MySQL中常用的操作语句已汇总他们的列名相同，按理来说student的classId必须遵循class表中的classId，也就是如果class表中没有的id，student中是不能有的。外连接：只能使用join on 的方式写，可以在join前头加上left/right的关键字，称为左连接/右连接。（2）offset：偏移量。对指定列进行分组操作：指定某个列，针对这个类，把值相同的行，分到一组中，可以针对每个组，分别进行聚合查询。查询每个岗位的平均工资，但是刨除平均工资超过2w的数据（条件是在聚合之后执行的，只能用having）阅读详情

汇总员工的上下级。
源表：
[备注]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　　　　。。。。。。。。。。。。。。。。。

麻烦各位高人了，谢谢:)

...全文

Tribon Vitesse开发教程（一） Tribon Vitesse简介 Tribon Vitesse是Tribon提供给Python编程语言的编程接口，它是一系列的API。使用Python的语法调用这些API，结合Python语言自身的语法生成的程序叫做Vitesse程序。Vitesse是法语“速度”的意思，读作“为戴斯”。使用Tribon Vitesse可以直接访问Tribon产品信息模型和使用Tribon应用程序的功能，以控制设计阅读详情

luckljtchinaren

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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

求一汇总语句

相关推荐