SQL子查询获取总数 [英] SQL subquery to get the total

问题描述

使用SQL子查询，我如何获得每个经理(包括他的团队)的总项目和总收入? 假设我有带有列的此表items_revenue:

Using SQL subquery, how do I get the total items and total revenue for each manager including his team? Suppose I have this table items_revenue with columns:

所有管理者(is_manager = 1)及其各自的成员都在上表中. Member1在Manager1下，Member2在Manager2下，依此类推，但实际数据是随机排列的.

All the managers (is_manager=1) and their respective members are in the above table. Member1 is under Manager1, Member2 is under Manager2, and so on, but real data are in random arrangement.

我希望我的查询输出ff.

I want my query to output the ff.:

这与 SQL查询以获取某些行的小计，但我不想使用CASE表达式.谢谢！

This is related to SQL query to get the subtotal of some rows but I don't want to use the CASE expression. Thanks!

您可以复制此内容以轻松创建表:

You can copy this to easily create the table:

DROP TABLE IF EXISTS items_revenue;
CREATE TABLE items_revenue (id int, is_manager int, manager_id int, name varchar(55), no_of_items int, revenue int);
INSERT INTO items_revenue (id, is_manager, manager_id, name, no_of_items, revenue)
VALUES
    (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);

推荐答案

使用union all并凝聚:

select manager_id, sum(no_of_items) as no_of_items, sum(revenue) as revenue
from ((select ir.manager_id, ir.no_of_items, ir.revenue
       from items_revenue ir
       where ir.is_manager = 0
      ) union all
      (select ir.id, ir.no_of_items, ir.revenue
       from items_revenue ir
       where ir.is_manager = 1
      )
     ) irm
group by manager_id;

注意:这仅处理表中的直接报告.这是您提供的示例数据.如果您需要所有直接报告，则问题将大为不同，因此请不要针对这种情况修改此问题(再询问).如果您需要它，那么MySQL并不是最好的工具(除非您使用的是版本8)，尽管您可以在知道最大深度的情况下解决它.

Note: This only handles direct reports in the table. This is the sample data that you provide. The problem is significantly different if you need all direct reports, so don't modify this question for that situation (ask another). If that is your need, then MySQL is not the best tool (unless you are using version 8), although you can solve it if you know the maximum depth.

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