SELECT
dayTb.cday AS datatime,
IFNULL(tNumTb.num, 0) num
FROM
(
SELECT
DATE_FORMAT(
@cdate := DATE_SUB(@cdate, INTERVAL + 1 MONTH),
'%Y-%m'
) cday
FROM
(
SELECT
@cdate := DATE_SUB(CURDATE(), INTERVAL - 1 MONTH)
FROM
ctg_indent
LIMIT 12
) t0
WHERE
DATE(@cdate) < DATE_SUB(NOW(), INTERVAL - 1 MONTH)
) dayTb
LEFT JOIN (
SELECT
DATE_FORMAT(t.updateDate, '%Y-%m') AS cday,
-- COUNT(DISTINCT t.id) num
SUM(total_price) num
FROM
ctg_indent t
WHERE
seller_ID = '7d2db429e00f11e886f744a84224b0ff'
AND state = 8
GROUP BY
YEAR (t.updateDate) DESC,
MONTH (t.updateDate) DESC
) tNumTb ON tNumTb.cday = dayTb.cday
ORDER BY
datatime ASC;
效果：
SELECT
CONCAT(
YEAR (dayTb.cday),
'-',
MONTH (dayTb.cday),
'-',
DAY (dayTb.cday)
) AS days,
IFNULL(tNumTb.tradeAmountByDay, 0) tradeAmountByDay
FROM
(
SELECT
@cdate := DATE_SUB(@cdate, INTERVAL + 1 DAY) cday
FROM
(
SELECT
@cdate := DATE_SUB(CURDATE(), INTERVAL - 1 DAY)
FROM
ctg_indent
LIMIT 31
) t0
WHERE
DATE(@cdate) <
DATE_SUB(NOW(), INTERVAL - 1 DAY)
) dayTb
LEFT JOIN (
SELECT
DATE(t.updateDate) cday,
SUM(total_price) tradeAmountByDay
FROM
ctg_indent t
WHERE
seller_ID = '7d2db429e00f11e886f744a84224b0ff'
GROUP BY
cday
) tNumTb ON tNumTb.cday = dayTb.cday
ORDER BY
days DESC;
效果
最终效果
网友评论