代码之家 › 专栏 › 技术社区 › Gee

MySQL每月将总金额与支出、收入、储蓄(3个不同的表)中的所有金额进行百分比比较

union select mysql

Gee · 技术社区 · 7 年前

表和查询示例: http://sqlfiddle.com/#!9/78b360/1

到目前为止,我可以从每个表中获得每月的总和:

SELECT *
FROM(
SELECT sum(amount) AS Total, month(date) AS Month FROM expenses
UNION ALL
SELECT sum(amount), month(date) FROM income
UNION ALL
SELECT sum(amount), month(date) FROM savings
GROUP BY month(date)) as grand_total

|   Total | Month |
|---------|-------|
| 1323232 |     9 |
|  300101 |     9 |
|   20000 |     9 |
|       1 |    10 |

如果我只看第9个月,我试图实现的是这样的目标:

|   Total | Month | Percent
|---------|-------|--------
| 1323232 |     9 |     81%
|  300101 |     9 |     18%
|   20000 |     9 |      1%

你能帮帮我吗?我在stackoverflow和其他网站上查看了许多答案,但没有找到我真正需要的。非常感谢你的帮助。非常感谢。

1 回复 | 直到 7 年前

Tim Biegeleisen 7 年前

当前查询的问题是,在 expenses 或 income 表,仅适用于 savings 桌子决赛 GROUP BY 做适用于一切。

我认为您需要做的是,首先对三个表中的每个表进行按月汇总,将这些结果合并在一起,然后再按月进行第二次汇总,得到每个月的总计。

SELECT
    month,
    SUM(total) AS month_total
FROM
(
    SELECT MONTH(date) AS month, SUM(amount) AS total
    FROM expenses
    GROUP BY MONTH(date)
    UNION ALL
    SELECT MONTH(date), SUM(amount)
    FROM income
    GROUP BY MONTH(date)
    UNION ALL
    SELECT MONTH(date), SUM(amount)
    FROM savings
    GROUP BY MONTH(date)
) t
-- WHERE month = 9         -- or whichever months you want to see
GROUP BY
    month
ORDER BY
    month

推荐文章