SQL查询以获取某些行的小计 [英] SQL query to get the subtotal of some rows

问题描述

如果我想获取每个经理(包括他的团队)的总项目和总收入，那SQL查询脚本将是什么?
假设我有此表items_revenue，其中包含列:

What would be the SQL query script if I want to get the total items and total revenue for each manager including his team?
Suppose I have this table items_revenue with columns:

| id |is_manager|manager_id| name |no_of_items| revenue | | 1 | 1 | 0 | Manager1 | 621 | 833 | | 2 | 1 | 0 | Manager2 | 458 | 627 | | 3 | 1 | 0 | Manager3 | 872 | 1027 | ... | 8 | 0 | 1 | Member1 | 1258 | 1582 | | 9 | 0 | 2 | Member2 | 5340 | 8827 | | 10 | 0 | 3 | Member3 | 3259 | 5124 |

| id |is_manager|manager_id| name |no_of_items| revenue | | 1 | 1 | 0 | Manager1 | 621 | 833 | | 2 | 1 | 0 | Manager2 | 458 | 627 | | 3 | 1 | 0 | Manager3 | 872 | 1027 | ... | 8 | 0 | 1 | Member1 | 1258 | 1582 | | 9 | 0 | 2 | Member2 | 5340 | 8827 | | 10 | 0 | 3 | Member3 | 3259 | 5124 |

所有经理及其各自的成员都在上面的视图表中. Member1在Manager1下，Member2在Manager2下，依此类推，但实际数据是随机排列的.我还有一个带managers的视图表，其中只过滤了管理器.我尝试通过以下查询获取每个团队经理的总数:
SELECT id, is_manager, manager_id, name, SUM(no_of_items) AS total_items, SUM(revenue) AS total_revenue FROM items_revenue t WHERE (SELECT m.id FROM managers m WHERE m.id = t.id) GROUP BY id HAVING id IN (SELECT m.id FROM managers m);

All the managers and their respective members are in the above view table. Member1 is under Manager1, Member2 is under Manager2, and so on, but real data are in random arrangement. I also have a view table with managers where I filtered only the managers. I tried getting the totals per team manager with this query:
SELECT id, is_manager, manager_id, name, SUM(no_of_items) AS total_items, SUM(revenue) AS total_revenue FROM items_revenue t WHERE (SELECT m.id FROM managers m WHERE m.id = t.id) GROUP BY id HAVING id IN (SELECT m.id FROM managers m);

但是，它仅返回每个经理的总数，而不获取其成员的总数.

However, it only returns the totals of each manager alone, it does not get the totals of their members.

我希望我的查询输出ff.表格:
| id |is_manager|manager_id| name |total_items| total_revenue | | 1 | 1 | 0 | Manager1 | 1879 | 2415 | | 2 | 1 | 0 | Manager2 | 5798 | 9454 | | 3 | 1 | 0 | Manager3 | 4131 | 6151 |

I want my query to output the ff. table:
| id |is_manager|manager_id| name |total_items| total_revenue | | 1 | 1 | 0 | Manager1 | 1879 | 2415 | | 2 | 1 | 0 | Manager2 | 5798 | 9454 | | 3 | 1 | 0 | Manager3 | 4131 | 6151 |

任何想法如何获得这个?任何帮助将不胜感激.谢谢！

Any ideas how to get this? Any help would be greatly appreciated. Thanks!

推荐答案

您可以使用CASE表达式进行汇总，如果manager_id为零，则使用id表达式形成一个组，否则使用manager_id .其余逻辑与您已经拥有的逻辑相似.

You can aggregate using a CASE expression, which forms a group using the id if the manager_id be zero, otherwise using the manager_id. The rest of the logic is similar to what you already have.

SELECT
    CASE WHEN manager_id = 0 THEN id ELSE manager_id END AS manager_id,
    MAX(CASE WHEN is_manager=1 THEN name END) AS name,
    SUM(no_of_items) AS total_items,
    SUM(revenue) AS total_revenue
FROM items_revenue
GROUP BY
    CASE WHEN manager_id = 0 THEN id ELSE manager_id END;

演示

一个侧面说明:我在GROUP BY子句中使用了一个函数，该函数不符合ANSI，因此可能无法在所有地方运行.要解决此问题，我们可以首先对您的表进行子查询以生成有效的经理组.然后，将我上面的答案用于此中间结果.

One side note: I used a function in the GROUP BY clause, which is not ANSI compliant and therefore may not run everywhere. To fix this, we can first subquery your table to generate the effective manager groups. Then, use my above answer against this intermediate result.

这篇关于SQL查询以获取某些行的小计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！