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

khbbv19g  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(462)

如何编写查询以获得以下输出(见表4)

  1. table user : role - 1:manager, 2:employee
  2. +----+------+-----------+--------+
  3. | id | name | role | salary |
  4. +----+------+-----------+--------+
  5. | 1 | a | 1 | 0 |
  6. | 2 | b | 1 | 0 |
  7. | 3 | c | 2 | 10 |
  8. | 4 | d | 2 | 20 |
  9. | 5 | e | 2 | 30 |
  10. | 6 | f | 2 | 40 |
  11. +----+------+-----------+--------+
  1. table city
  2. +----+--------+------+
  3. | id | name | type |
  4. +----+--------+------+
  5. | 1 | cityA | 1 |
  6. | 2 | cityB | 2 |
  7. +----+--------+------+
  1. table user_city_mapping
  2. +----+-----------+---------+
  3. | id | user_id | city_id |
  4. +----+-----------+---------+
  5. | 1 | 1 | 1 |
  6. | 2 | 2 | 1 |
  7. | 3 | 2 | 2 |
  8. | 4 | 3 | 1 |
  9. | 5 | 4 | 1 |
  10. | 6 | 5 | 2 |
  11. | 7 | 6 | 2 |
  12. +----+-----------+---------+
  1. output required
  2. +------+-------+
  3. | name | total |
  4. +------+-------+
  5. | a | 30 |
  6. | b | 100 |
  7. | c | 10 |
  8. | d | 20 |
  9. | e | 70 |
  10. | f | 70 |
  11. +------+-------+

用户“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”

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

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

  1. SELECT b.user_id, sum(salary)
  2. FROM user_city_mapping a
  3. INNER JOIN user_city_mapping b
  4. ON a.city_id = b.city_id
  5. INNER JOIN user
  6. ON a.user_id = user.id AND role = 2
  7. GROUP BY b.user_id
kkbh8khc

kkbh8khc1#

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

  1. select distinct uname,
  2. case when salarybyuser = 0 then salarybycity
  3. else salarybyuser
  4. end salary
  5. from
  6. (
  7. select u.id uid,u.name uname,ucm.*,c.id cid,
  8. case when c.type = 1 then salary
  9. when c.type = 2 and role <> 1 then
  10. (select sum(salary)
  11. from user u
  12. join user_city_mapping ucm on ucm.user_id = u.id
  13. join city c on c.id = ucm.city_id
  14. where c.type = 2 and role <> 1
  15. )
  16. else 0
  17. end as salarybyuser,
  18. x.salarybycity
  19. from user u
  20. join user_city_mapping ucm on ucm.user_id = u.id
  21. join city c on c.id = ucm.city_id
  22. left join
  23. (
  24. select ucm.user_id uid,u.name, sum(SalaryByCity) salarybycity
  25. from user_city_mapping ucm
  26. join user u on u.id = ucm.user_id
  27. join
  28. (
  29. select c1.id cid,sum(salary) SalaryByCity
  30. from user u1
  31. join user_city_mapping ucm1 on ucm1.user_id = u1.id
  32. join city c1 on c1.id = ucm1.city_id
  33. group by c1.id
  34. ) s on s.cid = ucm.city_id
  35. where u.role = 1
  36. group by u.id,u.name
  37. ) x on x.uid = u.id
  38. order by c.id,u.role,u.id
  39. ) y
  40. order by uid
  41. ;
  42. +-------+--------+
  43. | uname | salary |
  44. +-------+--------+
  45. | a | 30 |
  46. | b | 100 |
  47. | c | 10 |
  48. | d | 20 |
  49. | e | 70 |
  50. | f | 70 |
  51. +-------+--------+
  52. 6 rows in set (0.002 sec)

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

展开查看全部

相关问题