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, -- 這些字段該怎么算自己調(diào)整
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]
SQL2005語(yǔ)句轉(zhuǎn)換成oracle語(yǔ)句 按照某天 按照某月 分段查詢數(shù)據(jù) 希望懂sql和oracle的大神看看 幫忙改一下
慕森王
2018-12-07 01:10:04
