我有一个位置表
tbl_positions
id position
1 Driver
2 Lobby
3 Support
4 Constructor
在另一个表中我有users
tbl_workers
id name position status
1 John 2 3
2 Mike 3 2
3 Kate 2 3
4 Andy 1 0
i do request of positions
如果没有status,我选择这个查询的所有内容。
SELECT p.id, p.position, count(*) FROM tbl_positions as p
inner join tbl_workers as w on w.position=p.id
group by p.id, p.position
但现在我需要输出相同的查询,但也考虑状态,状态2=预订状态,3=放置。我需要在单个查询中得到这样的输出。
Position booked placed
Driver 0 0
Lobby 0 2
Support 1 0
Constructor 0 0
我尝试添加WHERE tbl_workers.status IN (2)
用于预订和WHERE tbl_workers.status IN (3)
用于放置,它在两个查询中工作,但没有运气将此加入一个查询
试试这个:
SELECT p.id,
p.position,
SUM(CASE w.Status WHEN 2 THEN 1 ELSE 0 END) AS booked,
SUM(CASE w.Status WHEN 3 THEN 1 ELSE 0 END) AS placed
FROM tbl_positions AS p LEFT JOIN tbl_workers AS w
ON w.position=p.id
GROUP BY p.id, p.position
虽然您可以使用单个SQL查询完成此操作,但使用PHP可能更直接。
使用当前查询并将status
添加到GROUP BY
。
SELECT p.id, p.position, w.status, count(*) FROM tbl_positions as p
inner join tbl_workers as w on w.position=p.id
group by p.id, p.position, w.status
您将得到如下的结果集:
position status count
Driver 2 0
Lobby 2 0
Support 2 1
Constructor 2 0
Driver 3 0
Lobby 3 2
Support 3 0
Constructor 3 0
使用PHP逻辑判断2 = booked
和3 = placed