日期:2014-05-16 浏览次数:20698 次
WITH a1 (cz_time,coud) AS
(
SELECT CAST('2014-04-08 10:22:45.780' AS DATETIME) ,1 UNION ALL
SELECT '2014-04-08 11:22:45.780',1 UNION ALL
SELECT '2014-04-08 13:22:45.780',1 UNION ALL
SELECT '2014-04-08 14:22:45.780',1 UNION ALL
SELECT '2014-04-08 15:22:45.780',1 UNION ALL
SELECT '2014-04-09 11:22:45.780',1 UNION ALL
SELECT '2014-04-09 12:22:45.780',1 UNION ALL
SELECT '2014-04-09 13:22:45.780',1 UNION ALL
SELECT '2014-04-09 14:22:45.780',1 UNION ALL
SELECT '2014-04-09 15:22:45.780',1
)
SELECT CONVERT(CHAR(10),cz_time,120) cz_date
,SUM(CASE WHEN RIGHT(Convert(VARCHAR(20),cz_time,0),2)='AM' THEN coud ELSE 0 END) AM
,SUM(CASE WHEN RIGHT(Convert(VARCHAR(20),cz_time,0),2)='PM' THEN coud ELSE 0 END) PM
FROM a1
GROUP BY CONVERT(CHAR(10),cz_time,120)