SELECT *
from(
SELECT
DATE_FORMAT(demand_time,'%Y-%m') month,count(id) as demandNum
FROM
test_data
GROUP BY month
union
SELECT date_format(CURDATE(),'%Y-%m') month, count(id) demandNum from test_data
union
SELECT date_format(date_sub(CURDATE(), INTERVAL 1 MONTH),'%Y-%m') month, '0' demandNum from test_data
union
SELECT date_format(date_sub(CURDATE(), INTERVAL 2 MONTH),'%Y-%m') month, '0' demandNum from test_data
union
SELECT date_format(date_sub(CURDATE(), INTERVAL 3 MONTH),'%Y-%m') month, '0' demandNum from test_data
union
SELECT date_format(date_sub(CURDATE(), INTERVAL 4 MONTH),'%Y-%m') month, '0' demandNum from test_data
union
SELECT date_format(date_sub(CURDATE(), INTERVAL 5 MONTH),'%Y-%m') month, '0' demandNum from test_data
union
SELECT date_format(date_sub(CURDATE(), INTERVAL 6 MONTH),'%Y-%m') month, '0' demandNum from test_data
union
SELECT date_format(date_sub(CURDATE(), INTERVAL 7 MONTH),'%Y-%m') month, '0' demandNum from test_data
union
SELECT date_format(date_sub(CURDATE(), INTERVAL 8 MONTH),'%Y-%m') month, '0' demandNum from test_data
union
SELECT date_format(date_sub(CURDATE(), INTERVAL 9 MONTH),'%Y-%m') month, '0' demandNum from test_data
union
SELECT date_format(date_sub(CURDATE(), INTERVAL 10 MONTH),'%Y-%m') month, '0' demandNum from test_data
union
SELECT date_format(date_sub(CURDATE(), INTERVAL 11 MONTH),'%Y-%m') month, '0' demandNum from test_data
union
SELECT date_format(date_sub(CURDATE(), INTERVAL 12 MONTH),'%Y-%m') month, '0' demandNum from test_data
)
t
GROUP BY t.month
order by t.month desc
mysql获取连续一年数据
最新推荐文章于 2023-10-16 18:13:30 发布