输入"重置"记录后的MySQL PHP计数方式

Question

我在尝试弄清楚如何构建查询以允许我"重置"并仅在重置后计算记录时遇到麻烦。

基本结构

Log Table
ID | Date        | Time     | SectorID | personnumber
1  | 2020-02-10  | 13:23:00 | 23       | 66 (This is a row to be counted)
2  | 2020-02-10  | 13:28:00 | 38       | 66 (This is a row to be counted)
3  | 2020-02-10  | 13:30:00 | 5        | 66 (This is a 'reset' row) (SectorID 5 is a reset)
4  | 2020-02-10  | 13:38:00 | 12       | 66 (This is a row to be counted)
5  | 2020-02-10  | 13:42:00 | 56       | 66 (This is a row to be counted)

对于上述情况，有 2 条记录，然后进行了重置(由扇区 ID 5 表示(，然后又有 2 条记录(其他记录可以是 5 以外的任何其他数字(。

所以我希望"计数"返回 2

下面的查询是我在没有任何重置功能的情况下计算所有记录的查询

SELECT
personnumber,
count(*) as occurrences
FROM log
WHERE personnumber IS NOT NULL
AND sectorid != 5
GROUP BY personnumber
HAVING count(*) > 1
ORDER BY occurrences DESC, personnumber

这将返回

Personnumber | Occurrences
66           | 4

我希望这足以解释我的问题。任何帮助将不胜感激。 谢谢 乔恩

Answer 1

您可以使用sectorId和personnumeberJOIN同一个表，以便计算此 id 之后的所有记录：

SELECT
l.personnumber,
COUNT(*) as occurrences
FROM 
`log` l
INNER JOIN 
(
SELECT id, personnumber 
FROM   `log` ll 
WHERE  ll.sectorid = 5
) AS ll ON l.personnumber = ll.personnumber
WHERE 
l.personnumber IS NOT NULL
AND l.id > ll.id
GROUP BY l.personnumber
HAVING COUNT(*) > 1
ORDER BY 
occurrences DESC, 
l.personnumber

输出：

+--------------+-------------+
| personnumber | occurrences |
+--------------+-------------+
|           66 |           2 |
+--------------+-------------+
1 row in set (0.01 sec)

如果只需要特定personnumber最后一次出现扇区 5 之后的记录，则需要获取派生表中的最大 id：

SELECT
l.personnumber,
COUNT(*) as occurrences
FROM 
`log` l
INNER JOIN 
(
SELECT MAX(id) AS id, personnumber 
FROM   `log` ll 
WHERE  ll.sectorid = 5
GROUP BY personnumber
) AS ll ON l.personnumber = ll.personnumber
WHERE 
l.personnumber IS NOT NULL
AND l.id > ll.id
GROUP BY l.personnumber
HAVING COUNT(*) > 1
ORDER BY 
occurrences DESC, 
l.personnumber