非常复杂的交叉表查询，请各位老大帮忙看看。给100分

Question

select identity(int,1,1) as id,0 as nid,* into #t from 表 order by Sname,dt,Stimeupdate a
set
    nid = b.cnt
from
    #t a,
    (select c.*,count(d.Sname) as cnt 
     from #t c,#t d 
     where c.Sname=d.Sname and c.dt=d.dt and c.Stime>=d.Stime) 
where
    a.Sname = b.Sname and a.dt = b.dt and a.Stime = b.Stimeselect 
    Sname,
    dt,
    T1    = max(case nid when 1 then Stime end),
    T2    = max(case nid when 2 then Stime end),
    T3    = max(case nid when 3 then Stime end),
    T4    = max(case nid when 4 then Stime end),
    T5    = max(case nid when 5 then Stime end),
    T6    = max(case nid when 6 then Stime end),
    SHour =   isnull(datediff( hh ,max(case nid when 1 then Stime end),max(case nid when 2 then Stime end)),0)
            + isnull(datediff( hh ,max(case nid when 3 then Stime end),max(case nid when 4 then Stime end)),0)
            + isnull(datediff( hh ,max(case nid when 5 then Stime end),max(case nid when 6 then Stime end)),0)
from
    #t
group by
    Sname,dt

Answer 1

--生成测试数据
create table #t1(Sname varchar(20),dt datetime,Stime datetime)
insert into #t1 select '张三','2005-03-11','07:36:00'
insert into #t1 select '张三','2005-03-11','12:01:00'
insert into #t1 select '张三','2005-03-11','12:39:00'
insert into #t1 select '张三','2005-03-11','19:11:00'
insert into #t1 select '李四','2005-03-11','07:30:00'
insert into #t1 select '李四','2005-03-11','12:06:00'
insert into #t1 select '李四','2005-03-11','12:36:00'
insert into #t1 select '李四','2005-03-11','17:36:00'
insert into #t1 select '李四','2005-03-11','19:36:00'
insert into #t1 select '王五','2005-03-11','07:55:00'
insert into #t1 select '王五','2005-03-11','12:01:00'
insert into #t1 select '王五','2005-03-11','12:44:00'
insert into #t1 select '王五','2005-03-11','17:04:00'
insert into #t1 select '王五','2005-03-11','17:55:00'
insert into #t1 select '王五','2005-03-11','23:03:00'
--执行交叉表查询
select identity(int,1,1) as id,0 as nid,* into #t from #t1 order by Sname,dt,Stimeupdate a
set
    nid = b.cnt
from
    #t a,
    (select 
         c.Sname,c.dt,c.Stime,count(d.Sname) as cnt 
     from 
         #t c,#t d 
     where 
         c.Sname=d.Sname and c.dt=d.dt and c.Stime>=d.Stime 
     group by 
         c.Sname,c.dt,c.Stime) b
where
    a.Sname = b.Sname and a.dt = b.dt and a.Stime = b.Stimeselect 
    Sname,
    dt,
    T1    = max(case nid when 1 then Stime end),
    T2    = max(case nid when 2 then Stime end),
    T3    = max(case nid when 3 then Stime end),
    T4    = max(case nid when 4 then Stime end),
    T5    = max(case nid when 5 then Stime end),
    T6    = max(case nid when 6 then Stime end),
    SHour =   isnull(datediff( mi ,max(case nid when 1 then Stime end),max(case nid when 2 then Stime end))/60,0)
            + isnull(datediff( mi ,max(case nid when 3 then Stime end),max(case nid when 4 then Stime end))/60,0)
            + isnull(datediff( mi ,max(case nid when 5 then Stime end),max(case nid when 6 then Stime end))/60,0)
from
    #t
group by
    Sname,dt
--输出结果
SName  Dt          T1        T2        T3        T4        T5        T6        SHour
-----  ----------  --------  --------  --------  --------  --------  --------  -----
李四   2005-03-11  07:30:00  12:06:00  12:36:00  17:36:00  19:36:00  NULL      9
王五   2005-03-11  07:55:00  12:01:00  12:44:00  17:04:00  17:55:00  23:03:00  13
张三   2005-03-11  07:36:00  12:01:00  12:39:00  19:11:00  NULL      NULL      10

Answer 2

--生成测试数据
create table #t1(Sname varchar(20),dt datetime,Stime datetime)
insert into #t1 select '张三','2005-03-11','07:36:00'
insert into #t1 select '张三','2005-03-11','12:01:00'
insert into #t1 select '张三','2005-03-11','12:39:00'
insert into #t1 select '张三','2005-03-11','19:11:00'
insert into #t1 select '李四','2005-03-11','07:30:00'
insert into #t1 select '李四','2005-03-11','12:06:00'
insert into #t1 select '李四','2005-03-11','12:36:00'
insert into #t1 select '李四','2005-03-11','17:36:00'
insert into #t1 select '李四','2005-03-11','19:36:00'
insert into #t1 select '王五','2005-03-11','07:55:00'
insert into #t1 select '王五','2005-03-11','12:01:00'
insert into #t1 select '王五','2005-03-11','12:44:00'
insert into #t1 select '王五','2005-03-11','17:04:00'
insert into #t1 select '王五','2005-03-11','17:55:00'
insert into #t1 select '王五','2005-03-11','23:03:00'
--执行交叉表查询
select identity(int,1,1) as id,0 as nid,* into #t from #t1 order by Sname,dt,Stimeupdate a
set
    nid = b.cnt
from
    #t a,
    (select 
         c.Sname,c.dt,c.Stime,count(d.Sname) as cnt 
     from 
         #t c,#t d 
     where 
         c.Sname=d.Sname and c.dt=d.dt and c.Stime>=d.Stime 
     group by 
         c.Sname,c.dt,c.Stime) b
where
    a.Sname = b.Sname and a.dt = b.dt and a.Stime = b.Stimeselect 
    Sname,
    dt,
    T1    = max(case nid when 1 then Stime end),
    T2    = max(case nid when 2 then Stime end),
    T3    = max(case nid when 3 then Stime end),
    T4    = max(case nid when 4 then Stime end),
    T5    = max(case nid when 5 then Stime end),
    T6    = max(case nid when 6 then Stime end),
    SHour =   isnull(datediff( mi ,max(case nid when 1 then Stime end),max(case nid when 2 then Stime end))/60,0)
            + isnull(datediff( mi ,max(case nid when 3 then Stime end),max(case nid when 4 then Stime end))/60,0)
            + isnull(datediff( mi ,max(case nid when 5 then Stime end),max(case nid when 6 then Stime end))/60,0)
from
    #t
group by
    Sname,dt
--输出结果
SName  Dt          T1        T2        T3        T4        T5        T6        SHour
-----  ----------  --------  --------  --------  --------  --------  --------  -----
李四   2005-03-11  07:30:00  12:06:00  12:36:00  17:36:00  19:36:00  NULL      9
王五   2005-03-11  07:55:00  12:01:00  12:44:00  17:04:00  17:55:00  23:03:00  13
张三   2005-03-11  07:36:00  12:01:00  12:39:00  19:11:00  NULL      NULL      10