零/零结果的MySQL GroupBy

Question

我现在正在写一个有三个表的票务系统

一份供用户使用：

users
    +----+-----------+----------+
    | ID | FirstName | LastName |
    +----+-----------+----------+
    |  1 | First     | User     |
    |  2 | Second    | User     |
    |  3 | Third     | User     |
    |  4 | Fourth    | User     |
    |  5 | Fifth     | User     |
    +----+-----------+----------+

一张票：

ticket
    +----+---------------+
    | ID | TicketSubject |
    +----+---------------+
    |  1 | Ticket #1     |
    |  2 | Ticket #2     |
    |  3 | Ticket #3     |
    |  4 | Ticket #4     |
    +----+---------------+

另一种方法是为用户分配操作票(每个票证可以有多个用户)：

ticket_assigned
    +----+----------+--------+
    | ID | TicketID | UserID |
    +----+----------+--------+
    |  1 |        1 |      1 |
    |  2 |        1 |      2 |
    |  3 |        2 |      1 |
    |  4 |        3 |      5 |
    |  5 |        3 |      3 |
    +----+----------+--------+

我试图创建一个摘要来显示每个用户，以及他们给他们分配了多少张票，例如：

+------------+-------+
|    Name    | Count |
+------------+-------+
| First      |     2 |
| Second     |     1 |
| Third      |     1 |
| Fourth     |     0 |
| Fifth      |     1 |
| Unassigned |     2 |
+------------+-------+

请注意，最后一个条目是“未分配的”，这是票证表中没有出现在ticket_assigned表中的记录数(因此，未赋值)。还请进一步注意，用户“第四”为零，因为该用户在ticket_assigned表中没有记录。

下面是我使用的当前MySQL查询：

SELECT 
   CASE 
      WHEN users.FirstName IS NULL 
      THEN 'Unassigned' 
      ELSE users.FirstName 
   END as 'UserName',
   COUNT(*) as 'TicketCount' 
FROM tickets 
LEFT OUTER JOIN ticket_assigned ON tickets.ticket_id = ticket_assigned.ticket_id 
LEFT OUTER  JOIN users ON ticket_assigned.user_id = users.user_id 
GROUP BY ticket_assigned.user_id 
ORDER BY UserName;

这方面的问题是，它没有显示ticket_assigned表中没有功能的任何用户，我基本上得到了以下内容：

+------------+-------+
|    Name    | Count |
+------------+-------+
| First      |     2 |
| Second     |     1 |
| Third      |     1 |
| Fifth      |     1 |
| Unassigned |     2 |
+------------+-------+

是否有人能够帮助并告诉我如何修改我的查询以包括ticket_assigned表中没有记录的用户？提前感谢！

Answer 1

使用带有子查询的LEFT JOIN来聚合票证：

SELECT t1.FirstName,
       COALESCE(t2.ticket_count, 0) AS num_tickets
FROM users t1
LEFT JOIN
(
    SELECT UserID, COUNT(*) AS ticket_count
    FROM ticket_assigned
    GROUP BY UserID
) t2
    ON t1.ID = t2.UserID
UNION ALL
SELECT 'Unassigned', COUNT(*)
FROM tickets t
WHERE NOT EXISTS (SELECT 1 FROM tickets_assigned ta
                  WHERE ta.ticketId = t.id)

Answer 2

在MySQL中，我认为你需要一个left join和union all

select u.id, u.firstname, count(ta.userId) as num_tickets
from users u left join
     tickets_assigned ta
     on ta.userId = u.id
group by u.id, u.firstname
union all
select NULL, 'Unassigned', count(*)
from tickets t
where not exists (select 1
                  from tickets_assigned
                  where ta.ticketId = t.id
                 );

我在聚合中包含了u.id。我不愿意仅仅用名字来聚合(和报告)，因为不同的人经常有相同的名字，即使在一个相对较小的群体中也是如此。

Answer 3

SELECT 
    u2.Firstname, IFNULL(tmp.count, 0) AS count
FROM users u2 
LEFT JOIN (
    SELECT u.id, u.Firstname, COUNT(1) as count
    FROM ticket_assigned ta 
    LEFT JOIN ticket t ON t.id = ta.ticketID
    LEFT JOIN users u ON  u.id = ta.userID
    GROUP BY u.id
    ) tmp ON tmp.id = u2.id
UNION 

SELECT
    'Unassigned', count(1) AS count 
FROM ticket
    WHERE id NOT IN (SELECT ticketid FROM ticket_assigned)