I've got three tables. Tickets, ticket history, and classification. I'd like to count how many tickets employees have completed, but broken down by classification.
我有三张桌子。门票，门票历史和分类。我想算一下员工已完成的门票数量，但按分类细分了。
As an example, Employee A worked on 3 tickets. The first two tickets were classified as bugs and the last was a feature. I'd like to query the database and select all tickets where the employee changed the state of the ticket.
例如，员工A处理了3张票。前两张票被归类为错误，最后一张是特色。我想查询数据库并选择员工更改故障单状态的所有故障单。
Something like this:
像这样的东西：
Bug..... 2
错误..... 2
Feature...... 1
特色...... 1
UI..... 0
UI ..... 0
Request..... 0
请求..... 0
Right now, all I can get is this:
现在，我能得到的就是：
Bug..... 2
错误..... 2
Feature...... 1
特色...... 1
Using this query:
使用此查询：
SELECT c.id, c.description, count(c.id)
FROM classification c
LEFT JOIN ticket t on t.classification_id = c.id
LEFT JOIN ticket_history th on th.ticket_num = t.id
WHERE th.employee = '456'
AND th.from_state = '2' AND th.to_state = '3'
GROUP BY c.id
ORDER BY c.id
I've created a SQL Fiddle with sample data, but haven't managed to find a solution yet.
我已经创建了一个带有示例数据的SQL Fiddle，但尚未设法找到解决方案。
1 个解决方案
#1
3
The first problem is, that not all rows from the classification table get selected, but only those with corresponding entries in the other two tables. That is because you've put some conditions that depend on entries in those tables in the WHERE clause. Basically, you're doing a LEFT JOIN to address exactly this problem, but undermine this by putting the conditions in WHERE and not in LEFT JOIN ... ON.
第一个问题是，并非所有来自分类表的行都被选中，而只有那些在其他两个表中具有相应条目的行。那是因为你已经把一些条件依赖于WHERE子句中这些表中的条目。基本上，你正在进行LEFT JOIN来解决这个问题，但是通过将条件放在WHERE而不是LEFT JOIN ... ON中来破坏这个问题。
The second problem is, that you count the number of classification while you actually want the number of tickets, right? So change count(c.id) to count(t.id) to get the right numbers.
第二个问题是，你实际想要票数时计算分类数，对吧？因此，将count（c.id）更改为count（t.id）以获得正确的数字。
SELECT c.id, c.description, count(t.id)
FROM classification c
LEFT JOIN ticket t on t.classification_id = c.id
LEFT JOIN ticket_history th on th.ticket_num = t.id
AND th.employee = '456'
AND th.from_state = '2' AND th.to_state = '3'
GROUP BY c.id
ORDER BY c.id;
#1
3
The first problem is, that not all rows from the classification table get selected, but only those with corresponding entries in the other two tables. That is because you've put some conditions that depend on entries in those tables in the WHERE clause. Basically, you're doing a LEFT JOIN to address exactly this problem, but undermine this by putting the conditions in WHERE and not in LEFT JOIN ... ON.
第一个问题是，并非所有来自分类表的行都被选中，而只有那些在其他两个表中具有相应条目的行。那是因为你已经把一些条件依赖于WHERE子句中这些表中的条目。基本上，你正在进行LEFT JOIN来解决这个问题，但是通过将条件放在WHERE而不是LEFT JOIN ... ON中来破坏这个问题。
The second problem is, that you count the number of classification while you actually want the number of tickets, right? So change count(c.id) to count(t.id) to get the right numbers.
第二个问题是，你实际想要票数时计算分类数，对吧？因此，将count（c.id）更改为count（t.id）以获得正确的数字。
SELECT c.id, c.description, count(t.id)
FROM classification c
LEFT JOIN ticket t on t.classification_id = c.id
LEFT JOIN ticket_history th on th.ticket_num = t.id
AND th.employee = '456'
AND th.from_state = '2' AND th.to_state = '3'
GROUP BY c.id
ORDER BY c.id;