时间戳

首页 > 解决方案 > SQL - 拥有 COUNT() 不能按预期工作

mysql - SQL - 拥有 COUNT() 不能按预期工作

问题描述

我使用以下代码加入了几个表格并过滤了结果。

SELECT jmeno, prijmeni, adresa, vozidlo.spz, snimek.timestamp
FROM majitel
INNER JOIN vozidlo ON majitel.id_majitel = vozidlo.id_majitel
INNER JOIN kat_kamera ON kat_kamera.id_kategorie = vozidlo.id_kategorie
INNER JOIN snimek ON kat_kamera.id_kamera = snimek.id_kamera
WHERE TIMESTAMP >= TIMESTAMP(NOW() - INTERVAL 2 DAY)

这是结果。

jmeno   prijmeni    adresa  spz         timestamp
John    Doe         Prague  7A2 5109    17/05/2019 08:21
John    Doe         Prague  7A2 5109    17/05/2019 20:50
Vanessa Green       Pilsen  4P8 9370    17/05/2019 06:14
John    Doe         Prague  7A2 5109    17/05/2019 20:50
Vanessa Green       Pilsen  4P8 9370    17/05/2019 12:27
Vanessa Green       Pilsen  4P8 9370    17/05/2019 14:31
John    Doe         Prague  7A2 5109    18/05/2019 15:35

到目前为止，这运作良好。问题是，我只想将结果限制为出现 3 次或更多的这些结果。

所以我像这样修改了查询。

SELECT jmeno, prijmeni, adresa, vozidlo.spz, snimek.timestamp
FROM majitel
INNER JOIN vozidlo ON majitel.id_majitel = vozidlo.id_majitel
INNER JOIN kat_kamera ON kat_kamera.id_kategorie = vozidlo.id_kategorie
INNER JOIN snimek ON kat_kamera.id_kamera = snimek.id_kamera
WHERE TIMESTAMP >= TIMESTAMP(NOW() - INTERVAL 2 DAY)
HAVING COUNT(jmeno) >= 3

但不幸的是，这不起作用。因为它只返回这个。

jmeno   prijmeni    adresa  spz         timestamp
John    Doe         Prague  7A2 5109    17/05/2019 08:21

但最终结果中应该有John Doe 和Vanessa Green。

你能帮我得到想要的结果吗？

标签： mysqlsql

解决方案

你需要一个GROUP BY. MySQL 允许HAVING没有GROUP BY. 整个查询被认为是一个聚合查询并且只返回一行。

如果您只想要重复部分：

SELECT jmeno, prijmeni, adresa, v.spz
FROM majitel m INNER JOIN
     vozidlo v
     ON m.id_majitel = v.id_majitel INNER JOIN
     kat_kamera kk
     ON kk.id_kategorie = v.id_kategorie INNER JOIN
     snimek s
     ON kk.id_kamera = s.id_kamera
WHERE TIMESTAMP >= TIMESTAMP(NOW() - INTERVAL 2 DAY)
GROUP BY jmeno, prijmeni, adresa, v.spz
HAVING COUNT(*) >= 3;

（我鼓励您限定所有列名，以便清楚它们来自哪些表。）

包含起来比较timestamp棘手。也许将它们连接到同一行就足够了：

SELECT jmeno, prijmeni, adresa, v.spz,
       GROUP_CONCAT(timestamp) as timestamps
FROM majitel m INNER JOIN
     vozidlo v
     ON m.id_majitel = v.id_majitel INNER JOIN
     kat_kamera kk
     ON kk.id_kategorie = v.id_kategorie INNER JOIN
     snimek s
     ON kk.id_kamera = s.id_kamera
WHERE TIMESTAMP >= TIMESTAMP(NOW() - INTERVAL 2 DAY)
GROUP BY jmeno, prijmeni, adresa, v.spz
HAVING COUNT(*) >= 3;

如果您想要单独的行，那么在 MySQL 8+ 中，您可以使用窗口函数：

SELECT x.*
FROM (SELECT jmeno, prijmeni, adresa, v.spz, timestamp,
             COUNT(*) OVER (jmeno, prijmeni, adresa, v.spz) as cnt
      FROM majitel m INNER JOIN
           vozidlo v
           ON m.id_majitel = v.id_majitel INNER JOIN
           kat_kamera kk
           ON kk.id_kategorie = v.id_kategorie INNER JOIN
           snimek s
           ON kk.id_kamera = s.id_kamera
      WHERE TIMESTAMP >= TIMESTAMP(NOW() - INTERVAL 2 DAY)
      GROUP BY jmeno, prijmeni, adresa, v.spz
     ) x
WHERE cnt >= 3;

推荐阅读