Mysql:从多个连接表中选择特定数据

Question

我很难理解这个select语句。数据来自3个表(为了更容易阅读，我去掉了所有不必要的数据)：

mysql> describe vulnerability;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| vuln_id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| severity      | int(10) unsigned | NO   |     | NULL    |                |
| host_id       | int(10) unsigned | NO   | MUL | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+

mysql> describe cve;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| cve_id  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| cve     | varchar(15)      | NO   |     | NULL    |                |
| vuln_id | int(10) unsigned | NO   | MUL | NULL    |                |
| year    | int(4) unsigned  | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+

mysql> describe host;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| host_id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| ip_addr      | int(10) unsigned | NO   |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

我希望输出的主机数量低于2009年，严重性= 3。年份包含在CVE中，它与vuln_id FK的漏洞相关。该漏洞具有严重程度，并通过host_id FK绑定到主机。这是我到目前为止所知道的：

mysql> select count(distinct ip_addr) from host H 
  inner join vulnerability V on H.host_id = V.host_id 
  inner join CVE C on C.vuln_id = V.vuln_id 
  where V.severity = 3 and C.year < 2009;
+-------------------------+
| count(distinct ip_addr) |
+-------------------------+
|                    5071 |
+-------------------------+

这告诉我具有2009年以前的漏洞的主机总数，这是一个很好的开始。然而，我想更进一步，只包括那些有50个或更多漏洞的主机。我不知道该怎么做。host表中的每个主机条目都有多个对应的漏洞条目。我假设我需要在where子句中添加一些东西，但是我被卡住了。

提前谢谢。如果需要更多信息，请告诉我。

Answer 1

尝试使用GROUP BY和HAVING

SELECT ip_addr
FROM host AS H
INNER JOIN vulnerability AS V
    ON H.host_id = V.host_id
INNER JOIN CVE AS C
    ON C.vuln_id = V.vuln_id
WHERE V.severity = 3 AND C.year < 2009
GROUP BY ip_addr
HAVING COUNT(DISTINCT vuln_id) >= 50

要只获取计数，请将上面的查询包装在另一个查询中：

SELECT COUNT(*) FROM
(
     SELECT ip_addr
     FROM host AS H
     -- etc... same query as above
) T1