mysql - 第一次选择和第二次选择的值不同(UNION AND GROUP BY)

Question

我有一个表用户:

user_id | ui_id
--------+-------
   4    |   16
--------+-------
   5    |   17
--------+-------
   9    |   21

用户信息:

ui_id |      fname      |     lname
------+-----------------+--------------
  16  |     Joanalyn    |    Lalicon
------+-----------------+--------------
  17  |   Jose Allan    |   Dela Cruz
------+-----------------+--------------
  21  |       Steve     |   Dela Cruz

加类:

ot_id | approve_by
------+------------
  3   |     4
------+------------
  6   |     9
------+------------
  8   |     5
------+------------
  9   |     9
------+------------
  16  |     4

最后洛阿:

loa_id| approve_by
------+------------
  4   |     9
------+------------
  6   |     4

我想要将全名、qty 和qty2 作为一个查询中的字段获取。但无法让它发挥作用。我可以获得 ot_id 计数和 loa_id 计数的总和，但无法获取分开的值。

我的查询:

SELECT name,qty2 <---- value from second select
FROM
(SELECT CONCAT(ui.fname,' ',ui.lname) AS name,
        COUNT(o.ot_id) AS qty
FROM overtime o
INNER JOIN users u ON o.approve_by=u.user_id
INNER JOIN user_info ui ON u.ui_id=ui.ui_id
GROUP BY ui.ui_id

UNION ALL

SELECT CONCAT(ui.fname,' ',ui.lname) AS name,
        COUNT(l.loa_id) AS qty2 <----- can't get this value
FROM loa l
INNER JOIN users u ON l.approve_by=u.user_id
INNER JOIN user_info ui ON u.ui_id=ui.ui_id
GROUP BY ui.ui_id) ui

GROUP BY name

我无法获取 qty2，但 qty 正在工作。如果我选择 SUM(qty) ，它将对 ot_id 和 loa_id

进行求和

我想要这样的东西:

       Name          |        qty       |       qty2    
---------------------+------------------+-------------------
  Joanalyn Lalicon   |        2         |         1
---------------------+------------------+-------------------
 Jose Allan Dela Cruz|        1         |         0
---------------------+------------------+-------------------
   Steve Dela Cruz   |        2         |         1

Answer 1

试试这个:

SELECT CONCAT(ui.fname, ' ', ui.lname) AS name
     , COUNT(o.ot_id) AS qty
     , (SELECT COUNT(*) FROM loa WHERE approve_by = u.user_id) AS qty2
  FROM users u
  JOIN user_info ui ON ui.ui_id = u.ui_id
  LEFT JOIN overtime o ON o.approve_by = u.user_id
 GROUP BY u.user_id;

<强> SQL Fiddle