查询一天24小时的或者查询一个星期7天或者一年12个月,首先要画好格式,格式sql如下
SELECT
DATE_FORMAT(
@cdate := DATE_ADD(@cdate, INTERVAL - 1 HOUR),
'%y-%m-%d %H'
) HOUR
FROM
(
SELECT
@cdate := DATE_ADD(
DATE_FORMAT('起始时间', '%y-%m-%d %H'),
INTERVAL + 1 HOUR
)
FROM
表(任意一张超过24条数据的表)
) t0
LIMIT 24
下面是我查询营业额的sql
SELECT
'销售额' item,
DATE_FORMAT(t1.HOUR, '%H') as keykey,
ifNull(SUM(t2.payment)/100,0) value
FROM
(
SELECT
DATE_FORMAT(
@cdate := DATE_ADD(@cdate, INTERVAL - 1 HOUR),
'%y-%m-%d %H'
) HOUR
FROM
(
SELECT
@cdate := DATE_ADD(
DATE_FORMAT('2019-10-21 23:59:59'), '%y-%m-%d %H'),
INTERVAL + 1 HOUR
)
FROM
basic_log
) t0
LIMIT 24
) t1
LEFT JOIN (
SELECT
DATE_FORMAT(add_data_time, '%y-%m-%d %H') HOUR,
payment
FROM
t_order
WHERE 1 = 1 AND order_type = 'consume' AND order_source IN ('drinks', 'compote')
) t2 ON t1. HOUR = t2. HOUR
GROUP BY
t1. HOUR
ORDER BY
t1. HOUR
查询一周7天的时候,要将模板换一下
SELECT
'销售额' item,
DATE_FORMAT(t1.HOUR, '%d') as keykey,
ifNull(SUM(t2.payment)/100,0) value
FROM
(
SELECT
DATE_FORMAT(
@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY),
'%y-%m-%d'
) HOUR
FROM
(
SELECT
@cdate := DATE_ADD(
DATE_FORMAT('2019-10-21', '%y-%m-%d'),
INTERVAL + 1 DAY
)
FROM
basic_log
) t0
LIMIT 7
) t1
LEFT JOIN (
SELECT
DATE_FORMAT(add_data_time, '%y-%m-%d') HOUR,
payment
FROM
t_order
WHERE 1 = 1 AND order_type = 'consume' AND order_source IN ('drinks', 'compote')
) t2 ON t1. HOUR = t2. HOUR
GROUP BY
t1. HOUR
ORDER BY
t1. HOUR
查询一年12个月
SELECT
'销售额' item,
DATE_FORMAT(t1.HOUR, '%m') as keykey,
ifNull(SUM(t2.payment)/100,0) value
FROM
(
SELECT
DATE_FORMAT(
@cdate := DATE_ADD(@cdate, INTERVAL - 1 MONTH),
'%y-%m-%d'
) HOUR
FROM
(
SELECT
@cdate := DATE_ADD(
DATE_FORMAT('2019-12-31', '%y-%m-%d'),
INTERVAL + 1 MONTH
)
FROM
basic_log
) t0
LIMIT 12
) t1
LEFT JOIN (
SELECT
DATE_FORMAT(add_data_time, '%y-%m') HOUR,
payment
FROM
t_order
) t2 ON DATE_FORMAT(t1.HOUR, '%y-%m') = t2. HOUR
GROUP BY
t1. HOUR
ORDER BY
t1. HOUR
如果对您有帮助,麻烦点个赞,如果有问题欢迎指出