MySQL生成当天24小时sql
需求:由于查询时候可能要按小时来查询,如果某一个小时没有数据就显示为0 。
解决方案:
##当天时间段
SELECT DATE_FORMAT( DATE_SUB( DATE_FORMAT( NOW(),'%Y-%m-%d'),INTERVAL ( -(@i:=@i+1) ) HOUR ) ,'%Y-%m-%d %H') AS 'time'
FROM (
SELECT a FROM
(SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' ) AS a
JOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b
ON 1
) AS b ,(SELECT @i:=-1) AS i
效果图:
如有错误,请留言。