我有一个表用户:
user_id | ui_id
--------+-------
4 | 16
--------+-------
5 | 17
--------+-------
9 | 21
user_info:
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:
loa_id| approve_by
------+------------
4 | 9
------+------------
6 | 4
我想将Full Name
、qty
和qty2
作为一个查询中的字段。但无法使其工作。我可以得到ot_id count和lo_id count的总和,但不能得到分开的值。
我的查询:
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
试试这个:
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