一个SQL 统计的例子，用到 CASE

CREATE TABLE tb(ID INT,TIME datetime)
INSERT tb SELECT 1,'2005/01/24 16:20'
UNION ALL SELECT 2,'2005/01/23 22:45'
UNION ALL SELECT 3,'2005/01/23 0:30'
UNION ALL SELECT 4,'2005/01/21 4:28'
UNION ALL SELECT 5,'2005/01/20 13:22'
UNION ALL SELECT 6,'2005/01/19 20:30'
UNION ALL SELECT 7,'2005/01/19 18:23'
UNION ALL SELECT 8,'2005/01/18 9:14'
UNION ALL SELECT 9,'2005/01/18 18:04'
GO
--查询处理：
SELECT  [时段] = CASE WHEN GROUPING(b.TIME)=1 THEN 'Total' ELSE b.TIME  END,           
 [Mon]=SUM(CASE a.week WHEN 1 THEN 1 ELSE 0 END),
 [Tue]=SUM(CASE a.week WHEN 2 THEN 1 ELSE 0 END),
 [Wed]=SUM(CASE a.week WHEN 3 THEN 1 ELSE 0 END),
 [Thu]=SUM(CASE a.week WHEN 4 THEN 1 ELSE 0 END),
 [Fri]=SUM(CASE a.week WHEN 5 THEN 1 ELSE 0 END),
 [Sat]=SUM(CASE a.week WHEN 6 THEN 1 ELSE 0 END),
 [Sun]=SUM(CASE a.week WHEN 0 THEN 1 ELSE 0 END),
 [Total]=COUNT(a.week)
FROM(
 SELECT TIME=CONVERT(CHAR(5),dateadd(HOUR,-1,TIME),108)
  ,week=(@@datefirst+datepart(weekday,TIME)-1)%7
 FROM tb
)a RIGHT JOIN(                 
 SELECT id=1,a='16:00',b='19:59',TIME='[5pm - 9pm)' UNION ALL
 SELECT id=2,a='20:00',b='23:59',TIME='[9pm - 1am)' UNION ALL
 SELECT id=3,a='00:00',b='02:59',TIME='[1am - 4am)' UNION ALL
 SELECT id=4,a='03:00',b='07:29',TIME='[4am - 8:30am)' UNION ALL
 SELECT id=5,a='07:30',b='11:59',TIME='[8:30am - 1pm)' UNION ALL
 SELECT id=6,a='12:00',b='15:59',TIME='[1pm - 5pm)'
)b ON a.TIME>=b.a AND a.TIME<b.b
GROUP BY b.id,b.TIME WITH ROLLUP
HAVING GROUPING(b.TIME)=0 OR GROUPING(b.id)=1
GO
--删除测试
DROP TABLE tb