有表A如下：
ID TIME
1 2007-12-27 08:09
1 2007-12-27 08:09
1 2007-12-27 08:09
2 2007-12-27 08:10
2 2007-12-27 08:10
3 2007-12-27 08:11
3 2007-12-27 08:11
1 2007-12-27 20:17
1 2007-12-27 20:17
2 2007-12-27 18:10
1 2007-12-28 08:09
2 2007-12-28 08:10
2 2007-12-28 08:10
2 2007-12-28 08:10
3 2007-12-28 08:19
1 2007-12-28 20:17
1 2007-12-28 20:17
2 2007-12-28 18:10
要求筛选出如下表B：
ID date time
1 2007-12-27 08:09,08:09,08:09,20:17,20:17,
2 2007-12-27 08:10,08:10,18:10,
3 2007-12-27 08:11,08:11,
1 2007-12-28 08:09,20:17,20:17,
2 2007-12-28 08:10,08:10,8:10,18:10,
非常感谢各位高手的指点
------解决方案--------------------
用函数
- SQL code
create function fn_Times(@Id int,@Date datetime)returns varchar(200)asbegin declare @r varchar(200) select @r=isnull(@r+',','')+convert(varchar(5),[Time],108) from a where [email protected] and convert(varchar(10),[Time],120)[email protected] return @rendgo--调用select Id,convert(varchar(10),[Time],120) as [Date],dbo.fn_Times(Id,convert(varchar(10),[Time],120)) as [Time]from agroup by Id,convert(varchar(10),[Time],120)
------解决方案--------------------
- SQL code
create table test(Id int,time datetime)insert into test select 1, '2007-12-27 08:09' insert into test select 1, '2007-12-27 08:09' insert into test select 1, '2007-12-27 08:09' insert into test select 2, '2007-12-27 08:10' insert into test select 2, '2007-12-27 08:10' insert into test select 3, '2007-12-27 08:11' insert into test select 3, '2007-12-27 08:11' insert into test select 1, '2007-12-27 20:17' insert into test select 1, '2007-12-27 20:17' insert into test select 2, '2007-12-27 18:10' insert into test select 1, '2007-12-28 08:09' insert into test select 2, '2007-12-28 08:10' insert into test select 2, '2007-12-28 08:10' insert into test select 2, '2007-12-28 08:10' insert into test select 3, '2007-12-28 08:19' insert into test select 1, '2007-12-28 20:17' insert into test select 1, '2007-12-28 20:17' insert into test select 2, '2007-12-28 18:10' Create function F_String(@id int,@date varchar(10))returns varchar(1000)asbegindeclare @v varchar(1000)set @v=''select @[email protected]+convert(varchar(5),time,108)+',' from testwhere [email protected] and Convert(varchar(10),time,120)[email protected]return @vendselect distinct Id,Convert(varchar(10),time,120) as date, dbo.F_String(Id,Convert(varchar(10),time,120)) as time from testId date time----------- ---------- ----------------------------------------------------------------------------------------------------------------1 2007-12-27 08:09,08:09,08:09,20:17,20:17,1 2007-12-28 08:09,20:17,20:17,2 2007-12-27 08:10,08:10,18:10,2 2007-12-28 08:10,08:10,08:10,18:10,3 2007-12-27 08:11,08:11,3 2007-12-28 08:19,
------解决方案--------------------
- SQL code
select id,convert(varchar(10),time,120) as date, substring(convert(varchar(20),min(time),120),12,5) as starttime, case when id = 3 and (select count(1) from a where id = 3 and convert(varchar(10),time,120) = convert(varchar(10),t1.time,120)) = 1 then null else substring(convert(varchar(20),max(time),120),12,5) end as endtimeinto cfrom a t1group by id,convert(varchar(10),time,120)/*id date starttime endtime----------- ---------- ---------- ----------1 2007-12-27 08:09 20:172 2007-12-27 08:10 18:103 2007-12-27 08:11 08:111 2007-12-28 08:09 20:172 2007-12-28 08:10 18:103 2007-12-28 08:19 NULL(6 row(s) affected)*/