我有下表:日期、时间、事故
我得弄清楚夜以继日的事故数量。
这是如何在一天内得到它
从日期=‘2012-01-01’和日期<=‘2012-01-31’和小时>=‘00:00:00:00和HOUR<=’11:59:59 'GRUUP的车辆中选择日期、总和(事故)为“当日”
如何修改查询,使其在夜间也发生意外?
发布于 2012-03-06 07:43:54
我在这里,但是;
SELECT date,SUM(CASE WHEN HOUR>='00: 00:00' AND HOUR<=' 11:59:59
THEN accidents ELSE 0 END)
AS 'at day',
SUM(CASE WHEN HOUR>='12: 00:00' AND HOUR<=' 23:59:59
THEN accidents ELSE 0 END) AS 'at night'
FROM vehicles
WHERE DATE>='2012-01-01 'AND DATE<='2012-01-31'
GROUP BY date发布于 2012-03-06 07:40:43
在晚上和白天意味着一整天,所以只需去除小时的条件。
SELECT date, SUM(accidents) AS 'at day' FROM vehicles WHERE DATE>='2012-01-01 'AND DATE<='2012-01-31' GRUUP BY date发布于 2012-03-06 07:43:01
可以省略“小时”和“WHERE”的GROUP BY条件。
SELECT date,
IF(HOUR BETWEEN '00:00:00' AND '11:59:59','day','night') as period,
SUM(accidents) as numAccidents
FROM vehicles
WHERE Date >= '2012-01-01' AND DATE <= '2012-01-31'
GROUP BY Date, IF(HOUR BETWEEN '00:00:00' AND '11:59:59','day','night')这将使用IF(condition,then,else)结构计算“夜间”或“白天”(注意,取决于您可能不得不使用HOUR >= '00:00:00' AND HOUR <= '11:59:59'而不是BETWEEN的小时列的类型)。
然后按日期和日/夜进行分组,以获得日间和期间的总金额。
https://stackoverflow.com/questions/9579610
复制相似问题