每隔一个小时查询一个表的数据集怎么写？

Question

表coureseList
列uid用户id，tid老师ID，stiem上课时间，etime结束时间，roomid教室id
关联表teacher老师表，classRoom教室表
sql怎么写每个小时的教师空闲和教室空闲，

返回如下格式

• 时间 教室空闲数 空闲教室
  2016-06-26 8:00到9:00 3 教室3，教室5，教室9

时间依次类推每个小时一条！

教师查询同上！

最好是一次返回一周的数据！望大神指点！

Answer 1

DECLARE @cdate DATETIME=CONVERT(DATETIME, CONVERT(VARCHAR,GETDATE(),111)+' 1:00:00')
--SELECT @cdate
;WITH coureseList(uid,tid,stiem,etime,roomid) AS (
    SELECT '1',1,'2016-06-28 8:00:00','2016-06-28 9:00:00','A' UNION ALL
    SELECT '1',2,'2016-06-28 9:00:00','2016-06-28 10:00:00','B' UNION ALL
    SELECT '1',1,'2016-06-29 8:00:00','2016-06-29 9:00:00','C' UNION ALL
    SELECT '1',2,'2016-06-29 10:00:00','2016-06-29 11:00:00','C' UNION ALL
    SELECT '2',3,'2016-06-28 8:00:00','2016-06-28 9:00:00','D'
),timelist(d,s,e)AS (
     SELECT CONVERT(VARCHAR, DATEADD(hh,sv.number,@cdate),21) +N'至' + convert(varchar,DATEADD(hh,sv.number+1,@cdate),14) AS d
            ,DATEADD(hh,sv.number,@cdate) AS s,DATEADD(hh,sv.number+1,@cdate) AS e
     FROM MASTER.dbo.spt_values AS sv 
     WHERE sv.[type]='P' AND sv.number>0 AND sv.number<=24*7
    )
SELECT t.d,case when len(b.r)>1 then LEFT(b.r,LEN(b.r)-1) ELSE N'空闲' end FROM timelist AS t
OUTER APPLY (
    SELECT c.roomid+',' 
      FROM coureseList AS c WHERE DATEDIFF(hh,c.stiem,t.s)=0 AND DATEDIFF(hh,c.etime,t.e)=0 FOR XML PATH('')
) b(r)

/*
d (No column name)
2016-06-28 02:00:00.000至03:00:00:000 空闲
2016-06-28 03:00:00.000至04:00:00:000 空闲
2016-06-28 04:00:00.000至05:00:00:000 空闲
2016-06-28 05:00:00.000至06:00:00:000 空闲
2016-06-28 06:00:00.000至07:00:00:000 空闲
2016-06-28 07:00:00.000至08:00:00:000 空闲
2016-06-28 08:00:00.000至09:00:00:000 A,D
2016-06-28 09:00:00.000至10:00:00:000 B
2016-06-28 10:00:00.000至11:00:00:000 空闲
2016-06-28 11:00:00.000至12:00:00:000 空闲
2016-06-28 12:00:00.000至13:00:00:000 空闲
2016-06-28 13:00:00.000至14:00:00:000 空闲
2016-06-28 14:00:00.000至15:00:00:000 空闲
2016-06-28 15:00:00.000至16:00:00:000 空闲
2016-06-28 16:00:00.000至17:00:00:000 空闲
2016-06-28 17:00:00.000至18:00:00:000 空闲
2016-06-28 18:00:00.000至19:00:00:000 空闲
2016-06-28 19:00:00.000至20:00:00:000 空闲
2016-06-28 20:00:00.000至21:00:00:000 空闲
2016-06-28 21:00:00.000至22:00:00:000 空闲
2016-06-28 22:00:00.000至23:00:00:000 空闲
2016-06-28 23:00:00.000至00:00:00:000 空闲
2016-06-29 00:00:00.000至01:00:00:000 空闲
2016-06-29 01:00:00.000至02:00:00:000 空闲
2016-06-29 02:00:00.000至03:00:00:000 空闲
2016-06-29 03:00:00.000至04:00:00:000 空闲
2016-06-29 04:00:00.000至05:00:00:000 空闲
2016-06-29 05:00:00.000至06:00:00:000 空闲
2016-06-29 06:00:00.000至07:00:00:000 空闲
2016-06-29 07:00:00.000至08:00:00:000 空闲
2016-06-29 08:00:00.000至09:00:00:000 C
2016-06-29 09:00:00.000至10:00:00:000 空闲
2016-06-29 10:00:00.000至11:00:00:000 C
2016-06-29 11:00:00.000至12:00:00:000 空闲
2016-06-29 12:00:00.000至13:00:00:000 空闲
2016-06-29 13:00:00.000至14:00:00:000 空闲
2016-06-29 14:00:00.000至15:00:00:000 空闲
2016-06-29 15:00:00.000至16:00:00:000 空闲
2016-06-29 16:00:00.000至17:00:00:000 空闲
2016-06-29 17:00:00.000至18:00:00:000 空闲
2016-06-29 18:00:00.000至19:00:00:000 空闲
2016-06-29 19:00:00.000至20:00:00:000 空闲
2016-06-29 20:00:00.000至21:00:00:000 空闲
2016-06-29 21:00:00.000至22:00:00:000 空闲
2016-06-29 22:00:00.000至23:00:00:000 空闲
2016-06-29 23:00:00.000至00:00:00:000 空闲
2016-06-30 00:00:00.000至01:00:00:000 空闲
2016-06-30 01:00:00.000至02:00:00:000 空闲
2016-06-30 02:00:00.000至03:00:00:000 空闲
2016-06-30 03:00:00.000至04:00:00:000 空闲
2016-06-30 04:00:00.000至05:00:00:000 空闲
2016-06-30 05:00:00.000至06:00:00:000 空闲
2016-06-30 06:00:00.000至07:00:00:000 空闲
2016-06-30 07:00:00.000至08:00:00:000 空闲
2016-06-30 08:00:00.000至09:00:00:000 空闲
2016-06-30 09:00:00.000至10:00:00:000 空闲
2016-06-30 10:00:00.000至11:00:00:000 空闲
2016-06-30 11:00:00.000至12:00:00:000 空闲
2016-06-30 12:00:00.000至13:00:00:000 空闲
2016-06-30 13:00:00.000至14:00:00:000 空闲
2016-06-30 14:00:00.000至15:00:00:000 空闲
2016-06-30 15:00:00.000至16:00:00:000 空闲
2016-06-30 16:00:00.000至17:00:00:000 空闲
2016-06-30 17:00:00.000至18:00:00:000 空闲
2016-06-30 18:00:00.000至19:00:00:000 空闲
2016-06-30 19:00:00.000至20:00:00:000 空闲
2016-06-30 20:00:00.000至21:00:00:000 空闲
2016-06-30 21:00:00.000至22:00:00:000 空闲
2016-06-30 22:00:00.000至23:00:00:000 空闲
2016-06-30 23:00:00.000至00:00:00:000 空闲
2016-07-01 00:00:00.000至01:00:00:000 空闲
2016-07-01 01:00:00.000至02:00:00:000 空闲
2016-07-01 02:00:00.000至03:00:00:000 空闲
2016-07-01 03:00:00.000至04:00:00:000 空闲
2016-07-01 04:00:00.000至05:00:00:000 空闲
2016-07-01 05:00:00.000至06:00:00:000 空闲
2016-07-01 06:00:00.000至07:00:00:000 空闲
2016-07-01 07:00:00.000至08:00:00:000 空闲
2016-07-01 08:00:00.000至09:00:00:000 空闲
2016-07-01 09:00:00.000至10:00:00:000 空闲
2016-07-01 10:00:00.000至11:00:00:000 空闲
2016-07-01 11:00:00.000至12:00:00:000 空闲
2016-07-01 12:00:00.000至13:00:00:000 空闲
2016-07-01 13:00:00.000至14:00:00:000 空闲
2016-07-01 14:00:00.000至15:00:00:000 空闲
2016-07-01 15:00:00.000至16:00:00:000 空闲
2016-07-01 16:00:00.000至17:00:00:000 空闲
2016-07-01 17:00:00.000至18:00:00:000 空闲
2016-07-01 18:00:00.000至19:00:00:000 空闲
2016-07-01 19:00:00.000至20:00:00:000 空闲
2016-07-01 20:00:00.000至21:00:00:000 空闲
2016-07-01 21:00:00.000至22:00:00:000 空闲
2016-07-01 22:00:00.000至23:00:00:000 空闲
2016-07-01 23:00:00.000至00:00:00:000 空闲
2016-07-02 00:00:00.000至01:00:00:000 空闲
2016-07-02 01:00:00.000至02:00:00:000 空闲
2016-07-02 02:00:00.000至03:00:00:000 空闲
2016-07-02 03:00:00.000至04:00:00:000 空闲
2016-07-02 04:00:00.000至05:00:00:000 空闲
2016-07-02 05:00:00.000至06:00:00:000 空闲
2016-07-02 06:00:00.000至07:00:00:000 空闲
2016-07-02 07:00:00.000至08:00:00:000 空闲
2016-07-02 08:00:00.000至09:00:00:000 空闲
2016-07-02 09:00:00.000至10:00:00:000 空闲
2016-07-02 10:00:00.000至11:00:00:000 空闲
2016-07-02 11:00:00.000至12:00:00:000 空闲
2016-07-02 12:00:00.000至13:00:00:000 空闲
2016-07-02 13:00:00.000至14:00:00:000 空闲
2016-07-02 14:00:00.000至15:00:00:000 空闲
2016-07-02 15:00:00.000至16:00:00:000 空闲
2016-07-02 16:00:00.000至17:00:00:000 空闲
2016-07-02 17:00:00.000至18:00:00:000 空闲
2016-07-02 18:00:00.000至19:00:00:000 空闲
2016-07-02 19:00:00.000至20:00:00:000 空闲
2016-07-02 20:00:00.000至21:00:00:000 空闲
2016-07-02 21:00:00.000至22:00:00:000 空闲
2016-07-02 22:00:00.000至23:00:00:000 空闲
2016-07-02 23:00:00.000至00:00:00:000 空闲
2016-07-03 00:00:00.000至01:00:00:000 空闲
2016-07-03 01:00:00.000至02:00:00:000 空闲
2016-07-03 02:00:00.000至03:00:00:000 空闲
2016-07-03 03:00:00.000至04:00:00:000 空闲
2016-07-03 04:00:00.000至05:00:00:000 空闲
2016-07-03 05:00:00.000至06:00:00:000 空闲
2016-07-03 06:00:00.000至07:00:00:000 空闲
2016-07-03 07:00:00.000至08:00:00:000 空闲
2016-07-03 08:00:00.000至09:00:00:000 空闲
2016-07-03 09:00:00.000至10:00:00:000 空闲
2016-07-03 10:00:00.000至11:00:00:000 空闲
2016-07-03 11:00:00.000至12:00:00:000 空闲
2016-07-03 12:00:00.000至13:00:00:000 空闲
2016-07-03 13:00:00.000至14:00:00:000 空闲
2016-07-03 14:00:00.000至15:00:00:000 空闲
2016-07-03 15:00:00.000至16:00:00:000 空闲
2016-07-03 16:00:00.000至17:00:00:000 空闲
2016-07-03 17:00:00.000至18:00:00:000 空闲
2016-07-03 18:00:00.000至19:00:00:000 空闲
2016-07-03 19:00:00.000至20:00:00:000 空闲
2016-07-03 20:00:00.000至21:00:00:000 空闲
2016-07-03 21:00:00.000至22:00:00:000 空闲
2016-07-03 22:00:00.000至23:00:00:000 空闲
2016-07-03 23:00:00.000至00:00:00:000 空闲
2016-07-04 00:00:00.000至01:00:00:000 空闲
2016-07-04 01:00:00.000至02:00:00:000 空闲
2016-07-04 02:00:00.000至03:00:00:000 空闲
2016-07-04 03:00:00.000至04:00:00:000 空闲
2016-07-04 04:00:00.000至05:00:00:000 空闲
2016-07-04 05:00:00.000至06:00:00:000 空闲
2016-07-04 06:00:00.000至07:00:00:000 空闲
2016-07-04 07:00:00.000至08:00:00:000 空闲
2016-07-04 08:00:00.000至09:00:00:000 空闲
2016-07-04 09:00:00.000至10:00:00:000 空闲
2016-07-04 10:00:00.000至11:00:00:000 空闲
2016-07-04 11:00:00.000至12:00:00:000 空闲
2016-07-04 12:00:00.000至13:00:00:000 空闲
2016-07-04 13:00:00.000至14:00:00:000 空闲
2016-07-04 14:00:00.000至15:00:00:000 空闲
2016-07-04 15:00:00.000至16:00:00:000 空闲
2016-07-04 16:00:00.000至17:00:00:000 空闲
2016-07-04 17:00:00.000至18:00:00:000 空闲
2016-07-04 18:00:00.000至19:00:00:000 空闲
2016-07-04 19:00:00.000至20:00:00:000 空闲
2016-07-04 20:00:00.000至21:00:00:000 空闲
2016-07-04 21:00:00.000至22:00:00:000 空闲
2016-07-04 22:00:00.000至23:00:00:000 空闲
2016-07-04 23:00:00.000至00:00:00:000 空闲
2016-07-05 00:00:00.000至01:00:00:000 空闲
2016-07-05 01:00:00.000至02:00:00:000 空闲
*/