mysql联合查询取总数,SQL子查询获取总数

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:

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.

I want my query to output the ff.:

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);

解决方案

Use union all and aggreation:

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;

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.