MYSQL：当不存在值时返回零，每月分类

发布于 2024-08-20 21:59:39 字数 875 浏览 6 评论 0原文

我有以下两个表，用于记录支出并提供支出类别信息：

表交易：

+-------+--------+--------+
| month | cat_id | amount |
+-------+--------+--------+
|     1 |      2 |      3 |
|     1 |      2 |      8 |
|     2 |      1 |      7 |
|     2 |      1 |      5 |
+-------+--------+--------+

表类别：

+--------+-------------+
| cat_id | cat_desc    |
+--------+-------------+
|      1 | Stock       |
|      2 | Consumables |
+--------+-------------+

我想要构建一个查询，显示每个月每个类别的金额总和，即使没有该月该类别的支出如下：

+-------+-------------+--------+
| month | cat_desc    | amount |
+-------+-------------+--------+
|     1 | Stock       |      0 |
|     1 | Consumables |     11 |
|     2 | Stock       |     12 |
|     2 | Consumables |      0 |
+-------+-------------+--------+

我怀疑需要使用外部联接，但我还没有找到执行此操作的声明。

感谢您的帮助。

原文

I have the following two tables that record expenditure and provide expenditure category information:

Table transactions:

+-------+--------+--------+
| month | cat_id | amount |
+-------+--------+--------+
|     1 |      2 |      3 |
|     1 |      2 |      8 |
|     2 |      1 |      7 |
|     2 |      1 |      5 |
+-------+--------+--------+

Table categories:

+--------+-------------+
| cat_id | cat_desc    |
+--------+-------------+
|      1 | Stock       |
|      2 | Consumables |
+--------+-------------+

What I would like is to construct a query that displays a sum of the amounts for each category, for each month, even if there is no expenditure in that category for that month like this:

+-------+-------------+--------+
| month | cat_desc    | amount |
+-------+-------------+--------+
|     1 | Stock       |      0 |
|     1 | Consumables |     11 |
|     2 | Stock       |     12 |
|     2 | Consumables |      0 |
+-------+-------------+--------+

I suspect an outer join would need to be used but I haven't found a statement to do it yet.

Thank you for any help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寄风 2024-08-27 21:59:39

这应该为您提供正确的结果。内部选择准备了所有月份与所有类别的列表，LEFT JOIN 处理其余部分。

SELECT t.month, t.cat_desc, COALESCE(SUM(t2.amount), 0) amount
FROM (
  SELECT DISTINCT t.month, c.cat_id, c.cat_desc
  FROM categories c
  CROSS JOIN transactions t
) t
LEFT JOIN transactions t2 ON ( t2.month = t.month AND t2.cat_id = t.cat_id )
GROUP BY t.month, t.cat_desc

使用以下方法可能会获得更好的性能（仅在必要时使用 DISTINCT），但您必须尝试：

SELECT t.month, t.cat_desc, COALESCE(SUM(t2.amount), 0) amount FROM (
  SELECT t.month, c.cat_id, c.cat_desc
  FROM
  (SELECT DISTINCT month FROM transactions) t
  CROSS JOIN categories c
) t
LEFT JOIN transactions t2 ON ( t2.month = t.month AND t2.cat_id = t.cat_id )
GROUP BY t.month, t.cat_desc

This one should provide you with the correct result. The inner select prepares a list of all months combined with all categories, and the LEFT JOIN handles the rest.

SELECT t.month, t.cat_desc, COALESCE(SUM(t2.amount), 0) amount
FROM (
  SELECT DISTINCT t.month, c.cat_id, c.cat_desc
  FROM categories c
  CROSS JOIN transactions t
) t
LEFT JOIN transactions t2 ON ( t2.month = t.month AND t2.cat_id = t.cat_id )
GROUP BY t.month, t.cat_desc

Performance might be better with the following (using DISTINCT only where necessary), but you will have to try:

SELECT t.month, t.cat_desc, COALESCE(SUM(t2.amount), 0) amount FROM (
  SELECT t.month, c.cat_id, c.cat_desc
  FROM
  (SELECT DISTINCT month FROM transactions) t
  CROSS JOIN categories c
) t
LEFT JOIN transactions t2 ON ( t2.month = t.month AND t2.cat_id = t.cat_id )
GROUP BY t.month, t.cat_desc

回复收藏 0 原文

夏了南城 2024-08-27 21:59:39

SELECT c.cat_id, c.cat_desc,t.month, SUM(t.amount) 
FROM categories c 
LEFT JOIN transactions t ON (t.cat_id = c.cat_id)
GROUP BY c.cat_id,t.month

SELECT c.cat_id, c.cat_desc,t.month, SUM(t.amount) 
FROM categories c 
LEFT JOIN transactions t ON (t.cat_id = c.cat_id)
GROUP BY c.cat_id,t.month

回复收藏 0 原文

溇涏 2024-08-27 21:59:39

SELECT c.cat_id, c.cat_desc,t.month, SUM(t.amount)  
FROM categories c  
LEFT JOIN transactions t ON (t.cat_id = c.cat_id) 
GROUP BY t.month,c.cat_id 
Order By t.month

SELECT c.cat_id, c.cat_desc,t.month, SUM(t.amount)  
FROM categories c  
LEFT JOIN transactions t ON (t.cat_id = c.cat_id) 
GROUP BY t.month,c.cat_id 
Order By t.month

回复收藏 0 原文

~没有更多了~