表:
账号,房间,日期
A1,101,2006-03-01
A2,102,2006-03-01
A1,102,2006-03-01
A1,103,2006-03-01
A1,104,2006-03-01
A3,101,2006-03-01
A3,103,2006-03-01
如何查询出:
日期,账号,曾使用房间
2006-03-01,A1,101;102;103;104
2006-03-01,A2,102
2006-03-01,A3,101;103谢谢
账号,房间,日期
A1,101,2006-03-01
A2,102,2006-03-01
A1,102,2006-03-01
A1,103,2006-03-01
A1,104,2006-03-01
A3,101,2006-03-01
A3,103,2006-03-01
如何查询出:
日期,账号,曾使用房间
2006-03-01,A1,101;102;103;104
2006-03-01,A2,102
2006-03-01,A3,101;103谢谢
create table tb(
账号 varchar(10),
房间 varchar(10),
日期 varchar(10)
)
go
insert tb select 'A1','101','2006-03-01'
union select 'A2','102','2006-03-01'
union select 'A1','102','2006-03-01'
union select 'A1','103','2006-03-01'
union select 'A1','104','2006-03-01'
union select 'A3','101','2006-03-01'
union select 'A3','103','2006-03-01'--创建自定义函数
create function f_str(@zh varchar(10),@dt varchar(10))
returns varchar(8000)
AS
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+ rtrim(房间) from tb where 日期=@dt and 账号=@zh
return(stuff(@ret,1,1,''))
end
--调用:
select 日期,
账号,
曾使用房间=(dbo.f_str(账号,日期))
from tb
group by 日期,账号
order by 日期,账号
---结果
/****
日期 帐号 曾使用房间
2006-03-01 A1 101,102,103,104
2006-03-01 A2 102
2006-03-01 A3 101,103
***/