27,579
社区成员
发帖
与我相关
我的任务
分享
select convert(char(13),time,120)
from T_RadarStatistics
group by convert(char(13),time,120)
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
-- 这里自己补全24个时段
SELECT 22,'22:00:00---23:00:00' UNION ALL
SELECT 23,'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-04-07'
AND [Time] < '2015-04-08'
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]
select * from dbo.T_RadarStatistics
Avspeed Occupancy Volume Time
75.00 4.00 6 2015-04-07 15:40:27.107
50.00 4.16 4 2015-04-07 15:40:27.073
100.00 4.00 8 2015-04-07 15:40:27.143
0.00 0.00 0 2015-04-07 15:40:27.340
41.67 4.50 6 2015-04-07 15:40:27.290
25.02 1.66 2 2015-04-07 15:40:27.000
100.00 3.66 8 2015-04-07 15:40:27.190
75.00 4.33 5 2015-04-07 15:40:27.257
比如我选择一个日期为2015-04-07 那么根据帖子第一幅图的内容显示出来 数据源给rdlc报表