WITH t1([hour],title) AS ( SELECT 0,' 0:00:00--- 1:00:00' UNION ALL SELECT 1,' 1:00:00--- 2:00:00' UNION ALL SELECT 2,' 2:00:00--- 3:00:00' UNION ALL SELECT 3,' 3:00:00--- 4:00:00' UNION ALL SELECT 4,' 4:00:00--- 5:00:00' UNION ALL SELECT 5,' 5:00:00--- 6:00:00' UNION ALL SELECT 6,' 6:00:00--- 7:00:00' UNION ALL SELECT 7,' 7:00:00--- 8:00:00' UNION ALL SELECT 8,' 8:00:00--- 9:00:00' UNION ALL SELECT 9,' 9:00:00--- 10:00:00' UNION ALL SELECT 10,' 10:00:00--- 11:00:00' UNION ALL SELECT 11,' 11:00:00--- 12:00:00' UNION ALL SELECT 12,' 12:00:00--- 13:00:00' UNION ALL SELECT 13,'13:00:00---14:00:00' UNION ALL SELECT 14,'14:00:00---15:00:00' UNION ALL SELECT 15,'15:00:00---16:00:00' UNION ALL SELECT 16,'16:00:00---17:00:00' UNION ALL SELECT 17,'17:00:00---18:00:00' UNION ALL SELECT 18,'18:00:00---19:00:00' UNION ALL SELECT 19,'19:00:00---20:00:00' UNION ALL SELECT 20,'20:00:00---21:00:00' UNION ALL SELECT 21,'21:00:00---22:00:00' UNION ALL SELECT 22,'22:00:00---23:00:00' UNION ALL SELECT 22,'23:00:00---24:00:00' ) ,t2 AS ( SELECT DatePart(hour,[Time]) [hour], AVG(Avspeed) Avspeed, -- 這些字段該怎么算自己調整 SUM(Occupancy) Occupancy, SUM(Volume) Volume FROM dbo.T_RadarStatistics WHERE [Time] >= '2015-05-20' AND [Time] < '2015-05-21' GROUP BY DatePart(hour,[Time]) ) SELECT t1.title, t2.Avspeed, t2.Occupancy, t2.Volume FROM t1 LEFT JOIN t2 ON t1.[hour] = t2.[hour] ORDER BY t1.[hour] WITH t1([dd],title) AS ( SELECT 1,' 1日' UNION ALL SELECT 2,' 2日' UNION ALL SELECT 3,' 3日' UNION ALL SELECT 4,' 4日' UNION ALL SELECT 5,' 5日' UNION ALL SELECT 6,' 6日' UNION ALL SELECT 7,' 7日' UNION ALL SELECT 8,' 8日' UNION ALL SELECT 9,' 9日' UNION ALL SELECT 10,' 10日' UNION ALL SELECT 11,' 11日' UNION ALL SELECT 12,' 12日' UNION ALL SELECT 13,' 13日' UNION ALL SELECT 14,' 14日' UNION ALL SELECT 15,' 15日' UNION ALL SELECT 16,' 16日' UNION ALL SELECT 17,' 17日' UNION ALL SELECT 18,' 18日' UNION ALL SELECT 19,' 19日' UNION ALL SELECT 20,' 20日' UNION ALL SELECT 21,' 21日' UNION ALL SELECT 22,' 22日' UNION ALL SELECT 23,' 23日' UNION ALL SELECT 24,' 24日' UNION ALL SELECT 25,' 25日' UNION ALL SELECT 26,' 26日' UNION ALL SELECT 27,' 27日' UNION ALL SELECT 28,' 28日' UNION ALL SELECT 29,' 29日' UNION ALL SELECT 30,' 30日' UNION ALL SELECT 31,' 31日' ) ,t2 AS ( SELECT DatePart(dd,[Time]) dd, AVG(Avspeed) Avspeed, SUM(Occupancy) Occupancy, SUM(Volume) Volume FROM dbo.T_RadarStatistics where Time >= '2015-04-01 00:00:00' and Time <= '2015-04-30 23:59:59' and address='192.168.0.81' GROUP BY DatePart(dd,[Time]) ) SELECT t1.title, t2.Avspeed, t2.Occupancy, t2.Volume FROM t1 LEFT JOIN t2 ON t1.[dd] = t2.[dd] ORDER BY t1.[dd]