急问一个sql语句写法

Question

参考：
--生成测试数据
create table 表(部门 int,人员 varchar(20))
insert into 表 select 1,'张三'
insert into 表 select 1,'李四'
insert into 表 select 1,'王五'
insert into 表 select 2,'赵六'
insert into 表 select 2,'邓七'
insert into 表 select 2,'刘八'
go--创建用户定义函数
create function f_str(@department int)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+','+人员 from 表 where 部门 = @department
    set @ret = stuff(@ret,1,1,'')
    return @ret 
end
go
--执行
select 部门,人员=dbo.f_str(部门) from 表 group by 部门 order by 部门
go--输出结果
/*
部门  人员
----  --------------
1     张三,李四,王五
2     赵六,邓七,刘八
*/
--删除测试数据
drop function f_str
drop table 表
go

Answer 1

create table #tt (code varchar(1000),description varchar(1000),daynum int ,date datetime)insert into #tt select 'A','AAAAAA',4,'2008-5-15'
  union all select 'B','BBBBBB',8,'2008-5-15'
  union all select  'C','CCCCCC',4,'2008-5-15'
  union all  select  'D','DDDDDD',10,'2008-5-17'declare @code varchar(500),@date datetime ,@description varchar(5000),@daynum int
update #tt set @code=case when @date=date then @code+'-'+code else code end,
@description=case when @date=date then @description+'-'+description else description end,
@daynum=case when @date=date then @daynum+daynum else daynum end,
@date=date,
code=@code,
description=@description,
daynum=@daynum
select * from #tt  a where daynum in (select top 1 daynum from  #tt b where a.date=b.date order by date, daynum desc)
/*
code                                                                                                                                                                                                                                                             description                                                                                                                                                                                                                                                      daynum      date
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------------------
A-B-C                                                                                                                                                                                                                                                            AAAAAA-BBBBBB-CCCCCC                                                                                                                                                                                                                                             16          2008-05-15 00:00:00.000
D                                                                                                                                                                                                                                                                DDDDDD                                                                                                                                                                                                                                                           10          2008-05-17 00:00:00.000
*/drop table #tt

Answer 2

create table #tt (code varchar(1000),description varchar(1000),daynum int ,date datetime)insert into #tt select 'A','AAAAAA',4,'2008-5-15'
  union all select 'B','BBBBBB',8,'2008-5-15'
  union all select  'C','CCCCCC',4,'2008-5-15'
  union all  select  'D','DDDDDD',10,'2008-5-17'declare @code varchar(500),@date datetime ,@description varchar(5000),@daynum int  -------测试数据update #tt set @code=case when @date=date then @code+'-'+code else code end,
@description=case when @date=date then @description+'-'+description else description end,
@daynum=case when @date=date then @daynum+daynum else daynum end,
@date=date,
code=@code,
description=@description,
daynum=@daynum
select * from #tt  a where daynum in (select top 1 daynum from  #tt b where a.date=b.date order by date, daynum desc)
/*
A-B-C AAAAAA-BBBBBB-CCCCCC 16 2008-05-15 00:00:00.000
D DDDDDD 10 2008-05-17 00:00:00.000
*/

Answer 3

create table ta (code varchar(50),description varchar(50),daynum int,date datetime)
insert into ta select 'A','AAA',4,'2008/5/15'
insert into ta select 'B','BBB',4,'2008/5/15'
insert into ta select 'C','CCC',4,'2008/5/15'
insert into ta select 'D','DDD',4,'2008/5/16'
go
create function f_str(@date datetime)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+'/'+description from ta where date = @date
    set @ret = stuff(@ret,1,1,'')
    return @ret 
end
go
create function f_str1(@date datetime)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+code+'('+ltrim(len(description))+')' from ta where date = @date
    return @ret 
end
select dbo.f_str1(date),dbo.f_str(date),sum(daynum),date from ta group by date
drop table ta
drop function f_str1,f_str

Answer 4

create table ta (code varchar(50),description varchar(50),daynum int,date datetime)
insert into ta select 'A','AAA',4,'2008/5/15'
insert into ta select 'B','BBB',4,'2008/5/15'
insert into ta select 'C','CCC',4,'2008/5/15'
insert into ta select 'D','DDD',4,'2008/5/16'
go
create function f_str(@date datetime)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+'/'+description from ta where date = @date
    set @ret = stuff(@ret,1,1,'')
    return @ret 
end
go
create function f_str1(@date datetime)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+code+'('+ltrim(len(description))+')' from ta where date = @date
    return @ret 
end
go
select dbo.f_str1(date),dbo.f_str(date),sum(daynum),date from ta group by date
drop table ta
drop function f_str1,f_str
/*A(3)B(3)C(3)     AAA/BBB/CCC                                    12          2008-05-15 00:00:00.000
D(3)              DDD                                           4           2008-05-16 00:00:00.000
*/

Answer 5

更改()中内容create table ta (code varchar(50),description varchar(50),daynum int,date datetime)
insert into ta select 'A','AAA',4,'2008/5/15'
insert into ta select 'B','BBB',8,'2008/5/15'
insert into ta select 'C','CCC',4,'2008/5/15'
insert into ta select 'D','DDD',6,'2008/5/16'
go
create function f_str(@date datetime)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+'/'+description from ta where date = @date
    set @ret = stuff(@ret,1,1,'')
    return @ret 
end
go
create function f_str1(@date datetime)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+code+'('+ltrim(daynum)+')' from ta where date = @date
    return @ret 
end
go
select dbo.f_str1(date),dbo.f_str(date),sum(daynum),date from ta group by date
drop table ta
drop function f_str1,f_str

Answer 6

create table leave (code varchar(50),description varchar(50),daynum int,date datetime)
insert into leave select 'A','AAAAAA',4,'2008/5/15'
insert into leave select 'B','BBBBBB',8,'2008/5/15'
insert into leave select 'C','CCCCCC',4,'2008/5/15'
insert into leave select 'D','DDDDDD',6,'2008/5/16'
go
create function f_str(@date datetime)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+'/'+description from leave where date = @date
    set @ret = stuff(@ret,1,1,'')
    return @ret 
end
go
create function f_str1(@date datetime)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+'/'+ code+'('+ltrim(daynum)+')' from leave where date = @date
    set @ret = stuff(@ret,1,1,'')
    return @ret 
end
go
select dbo.f_str1(date),dbo.f_str(date),sum(daynum),date from leave group by date
drop table leave
drop function f_str1,f_str/*
A(4)/B(8)/C(4) AAAAAA/BBBBBB/CCCCCC 16 2008-05-15 00:00:00.000
D(6)         DDDDDD                 6 2008-05-16 00:00:00.000
*/