MYSQL之数据报表

SELECT f.month AS month, f.amount AS amount
    FROM (
        SELECT *
        FROM (
            SELECT month(t_dissipate) AS months, SUM(t_amount) AS amount
            FROM discuz.transaction
            WHERE year(t_dissipate) = '2018'
            GROUP BY month(t_dissipate)
        ) t
            RIGHT JOIN (
                SELECT x.month AS month
                FROM discuz.months x
                UNION
                SELECT month(t_dissipate) AS months
                FROM discuz.transaction
                GROUP BY month(t_dissipate), t_amount
            ) d
            ON t.months = d.month
    ) f

months表: 月份 int类型 1-12

transaction:数据表 　　:新增时间  t_dissipate datetime 若干条

　　　　　　　　　　  :数量  amount  int  对应新增时间条数

 

转载于:https://www.cnblogs.com/assistants/p/10157252.html