如何使用MySQL统计当月每日收益金额

灰太狼 2023-10-12 23:25 24阅读 0赞

![61ce656f42bb4044aaac679ff3ca3819.png][]

> 如果要实现上图这个效果，这个SQL应该怎么写呢？

**目录**

1、实现思路

2、SQL实现

--------------------

## 1、实现思路 ##

> 首先，我们先实现查询当月每日的日期 。然后将查询结果与需要统计的表进行左连接即可。

SELECT DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (DAY(CURRENT_DATE())-1) DAY) + INTERVAL (t4.i*1000 + t3.i*100 + t2.i*10 + t1.i) DAY) AS date
    FROM
        (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t1,
        (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t2,
        (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t3,
        (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t4
    WHERE
        DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (DAY(CURRENT_DATE())-1) DAY) + INTERVAL (t4.i*1000 + t3.i*100 + t2.i*10 + t1.i) DAY) <= LAST_DAY(CURRENT_DATE())

![48c75f50fe114352a42e14bb67c4126a.png][]

> 比如我的表结构是这样的。

### ![5f5129beff444613bf1572f19be783c9.png][] 2、SQL实现 ###

SELECT dates.date, IFNULL(SUM(cms_recharge_record.amount), 0) AS total
    FROM (
        SELECT DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (DAY(CURRENT_DATE())-1) DAY) + INTERVAL (t4.i*1000 + t3.i*100 + t2.i*10 + t1.i) DAY) AS date
        FROM
            (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t1,
            (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t2,
            (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t3,
            (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t4
        WHERE
            DATE(DATE_SUB(CURRENT_DATE(), INTERVAL (DAY(CURRENT_DATE())-1) DAY) + INTERVAL (t4.i*1000 + t3.i*100 + t2.i*10 + t1.i) DAY) <= LAST_DAY(CURRENT_DATE())
    ) AS dates
    LEFT JOIN cms_recharge_record ON DATE(dates.date) = DATE(cms_recharge_record.create_time) AND MONTH(dates.date) = MONTH(cms_recharge_record.create_time)
    GROUP BY dates.date
    ORDER BY dates.date;

SQL解释：（解释来源于ChatGPT）

1、首先，使用子查询生成一个包含当月每天日期的临时表，子查询中的四个子查询（t1, t2, t3, t4）用于生成从0到9的数字序列，通过计算日期的偏移量来生成每天的日期。这样可以确保每天都有日期记录。

2、然后，使用左连接（LEFT JOIN）将临时表（dates）与cms\_recharge\_record表进行连接。连接条件是日期相等，并且月份相等。

3、使用IFNULL函数来处理当日没有收益记录的情况，将收益金额设置为0。

4、最后，使用GROUP BY对日期进行分组，并使用SUM函数计算每日的收益金额。

5、使用ORDER BY对日期进行排序，以确保结果按照日期的升序排列。

![a310f54952f94e8d9899509e3d9fdefc.png][]

> 这样基本上就能达到我们的要求，最后将查询结果封装成自己需要的数据格式即可。

[61ce656f42bb4044aaac679ff3ca3819.png]: https://img-blog.csdnimg.cn/61ce656f42bb4044aaac679ff3ca3819.png
[48c75f50fe114352a42e14bb67c4126a.png]: https://img-blog.csdnimg.cn/48c75f50fe114352a42e14bb67c4126a.png
[5f5129beff444613bf1572f19be783c9.png]: https://img-blog.csdnimg.cn/5f5129beff444613bf1572f19be783c9.png
[a310f54952f94e8d9899509e3d9fdefc.png]: https://img-blog.csdnimg.cn/a310f54952f94e8d9899509e3d9fdefc.png