MYSQL 对具有连接的表进行多次计数

Question

好的，我有这个 SQL 查询

$query = "
            SELECT
                c.category, count(t.id_ticket) as ticket_count, count(tm.id_message) as message_count
            FROM
                tickets t
            LEFT JOIN
                ticketMessages tm
            ON
                t.id_ticket = tm.id_ticket
            LEFT JOIN
                categories c
            ON
                t.id_category = c.id_category
            GROUP BY
                t.id_category
        ";

所以基本上我有一张票表，每张票可以有多条消息，每张票都有一个类别。

现在让我们假设以下

我有2张红色的票，一共5条消息我在蓝色类别中有 4 张票，总共有 10 条消息我在黑色类别中有3张票，总共有7条消息

我想做的是展示这样的东西:

[0]=>
  array(4) {
    ["category"]=>
    string(12) "Red"
    ["id_ticket_count"]=>
    string(1) "2"
    ["id_message_count"]=>
    string(1) "5"
  }
[1]=>
array(4) {
  ["category"]=>
  string(12) "Blue"
  ["id_ticket_count"]=>
  string(1) "4"
  ["id_message_count"]=>
  string(2) "10"
}
[1]=>
array(4) {
  ["category"]=>
  string(12) "Black"
  ["id_ticket_count"]=>
  string(1) "3"
  ["id_message_count"]=>
  string(2) "7"
}

但是我的 SQL 为消息和票证返回相同的值，该值是消息，但我也想知道有多少票证。我假设计算 id_ticket。

如果我按 t.ticket_id 添加组，那么它会为每张票显示一个新的数组项目，所以我最终得到 2 个红色类别项目、4 个蓝色项目和 3 个黑色项目，但它应该只显示每个类别的 1 个项目和计数对于每个类别。

如果有帮助，表格看起来像这样

Category
id_category | category
1 | red
2 | blue
3 | black

Tickets
id_ticket | id_category 
1 | 1
2 | 1
3 | 2
4 | 2
5 | 2
6 | 2
7 | 3
8 | 3
9 | 3

Messages
id_message | id_ticket
1 | 1
2 | 1
3 | 2
4 | 2
5 | 2
6 | 3
7 | 3
8 | 3
9 | 4
10 | 4
11 | 4
12 | 5
13 | 5
14 | 5
15 | 6
16 | 7
17 | 7
18 | 8
19 | 8
20 | 9
21 | 9
22 | 9

Answer 1

您可以尝试使用 CASE WHEN 子句来更恰本地处理基数问题。据我所知，使用 count 会导致 NULL 值，因为那些没有分配消息的票据被计为 1 个误导性有效行。

也许是这样的

           SELECT
            c.category, count(t.id_ticket) as ticket_count,
            SUM(CASE WHEN tm.id_message IS NOT NULL THEN 1 ELSE 0 END) as message_count
        FROM
            tickets t
        LEFT JOIN
            ticketMessages tm
        ON
            t.id_ticket = tm.id_ticket
        LEFT JOIN
            categories c
        ON
            t.id_category = c.id_category
        GROUP BY
            t.id_category

我假设 tickets 和 ticketMessages 之间存在 1:many 关系。