带“on”if条件的内部连接查询

khbbv19g 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(396)

如何编写查询以获得以下输出（见表4）

table user : role - 1:manager, 2:employee
+----+------+-----------+--------+
| id | name | role      | salary |
+----+------+-----------+--------+
|  1 | a    | 1         |      0 |
|  2 | b    | 1         |      0 |
|  3 | c    | 2         |     10 |
|  4 | d    | 2         |     20 |
|  5 | e    | 2         |     30 |
|  6 | f    | 2         |     40 |
+----+------+-----------+--------+

table city
+----+--------+------+
| id | name   | type |
+----+--------+------+
|  1 | cityA  |    1 |
|  2 | cityB  |    2 |
+----+--------+------+

table user_city_mapping
+----+-----------+---------+
| id | user_id   | city_id |
+----+-----------+---------+
|  1 | 1         | 1       |
|  2 | 2         | 1       |
|  3 | 2         | 2       |
|  4 | 3         | 1       |
|  5 | 4         | 1       |
|  6 | 5         | 2       |
|  7 | 6         | 2       |
+----+-----------+---------+

output required
+------+-------+
| name | total |
+------+-------+
| a    |    30 |
| b    |   100 |
| c    |    10 |
| d    |    20 |
| e    |    70 |
| f    |    70 |
+------+-------+

用户“a”、“b”、“c”、“d”属于类型1的“citya”。
用户“b”、“e”、“f”属于类型2的“cityb”。
用户“c”和“d”属于管理器“a”
用户“c”、“d”、“e”、“f”属于经理“b”
对所需输出的说明：
当“c”和“d”都在用户“a”管理器之下时，用户“a”得到总共30个“c”和“d”之和
用户“b”得到总数100，这是“c”、“d”、“e”和“f”的总和，因为所有用户都属于用户“b”管理器
用户“c”和“d”得到10和20，这是他们自己的工资，属于“citya”，属于“type 1”
用户“e”和“f”得到总数70和70，这是属于“cityb”的雇员的工资总和，如果是“type 2”

In short,
if an employee falls under any manager, the manager get the sum of the salary of the employee under him.
if the employee belongs to "type 1" city he gets his salary.
if an employee belongs to the "type 2" city he gets the sum of all employees belongs to that city.

以上提供的细节是需求和输出。我无法获得所需输出的查询。
我试过的东西

SELECT b.user_id, sum(salary) 
  FROM user_city_mapping a 
 INNER JOIN user_city_mapping b 
    ON a.city_id = b.city_id 
 INNER JOIN user 
    ON a.user_id = user.id AND role = 2 
 GROUP BY b.user_id

sql mysql Join inner-join

来源：https://stackoverflow.com/questions/61944797/inner-join-query-with-on-if-condition

1条答案

按热度按时间

kkbh8khc1#

相当混乱，需要多次解析数据。最简单的方法是使用基于城市类型的相关子查询按用户计算薪资类型2的总薪资。在子查询x中，首先计算出一个城市的总数，然后按经理汇总，然后外部查询选择distinct以除去重复的经理行，从而得到按城市和谁管理该城市的薪资。

select distinct uname,
         case when salarybyuser = 0 then salarybycity
                else salarybyuser
         end salary
from
(
select u.id uid,u.name uname,ucm.*,c.id cid,
         case when c.type = 1 then salary
              when c.type = 2 and role <> 1 then
              (select sum(salary)
                from user u
                join user_city_mapping ucm on ucm.user_id = u.id
                join city c on c.id = ucm.city_id
                where c.type = 2 and role <> 1
                )
         else 0
         end as salarybyuser,
         x.salarybycity
from user u
join user_city_mapping ucm on ucm.user_id = u.id
join city c on c.id = ucm.city_id
left join
(
select ucm.user_id uid,u.name, sum(SalaryByCity) salarybycity
from   user_city_mapping ucm
join   user u on u.id = ucm.user_id 
join
(
select  c1.id cid,sum(salary) SalaryByCity
from user u1
join user_city_mapping ucm1 on ucm1.user_id = u1.id
join city c1 on c1.id = ucm1.city_id
group by c1.id
) s on s.cid = ucm.city_id
where u.role = 1
group by u.id,u.name
) x on x.uid = u.id
order by c.id,u.role,u.id
) y
order by uid
;

+-------+--------+
| uname | salary |
+-------+--------+
| a     |     30 |
| b     |    100 |
| c     |     10 |
| d     |     20 |
| e     |     70 |
| f     |     70 |
+-------+--------+
6 rows in set (0.002 sec)

看起来很混乱，但实际上是3个查询压缩为1个。

赞(0）回复(0）举报 2021-08-13

我来回答

带“on”if条件的内部连接查询

1条答案

相关问题

热门标签

最新问答