mysql - 使用 NOT-LIKE 子句对三个表进行 SQL 查询

Question

我需要提取不属于指定组的所有服务器。我有3个表:host(包含主机)、hostgroup_relation(包含主机id和主机组id)、hostgroup(包含主机组)

我可以获得关系，但我需要每个不属于 ID 为 180 的组成员的主机

主持人:

SELECT host_id,host_name FROM host LIMIT 10;
+---------+-------------------+
| host_id | host_name         |
+---------+-------------------+
|    1482 | AADSYNC1          |
|     442 | Acces-Point-Wifi  |
|    1916 | ADAUDIT1          |
|    1562 | ADMORA1           |
|    2247 | ADMRDS2           |
|    2226 | ADSECU1           |
|    1203 | ADSELFSERVICE1    |
|    1172 | ALFRESCO1         |
|    1841 | ALFRESCO2         |
|     172 | Antispam-Ironport |
+---------+-------------------+

主机组:

SELECT hg_id, hg_name FROM hostgroup LIMIT 10
+-------+----------------------+
| hg_id | hg_name              |
+-------+----------------------+
|    82 | Antivirus-Trend      |
|    65 | Autocoms             |
|    72 | Baies-de-stockage    |
|    78 | Consoles             |
|   192 | Databases-All        |
|   193 | Databases-Main       |
|    68 | Databases-MySql      |
|    67 | Databases-Oracle     |
|   181 | Databases-PostgreSql |
|    69 | Databases-SQLServer  |
+-------+----------------------+

主机/主机组关系:

SELECT * FROM hostgroup_relation LIMIT 10;
+--------+-----------------+--------------+
| hgr_id | hostgroup_hg_id | host_host_id |
+--------+-----------------+--------------+
|   5698 |              70 |         1167 |
|   6772 |              53 |         1167 |
|   6820 |             144 |         1369 |
|   6821 |              62 |         1369 |
|   6822 |              53 |         1369 |
|   6823 |              70 |         1369 |
|   6825 |              62 |         1370 |
|   6826 |              53 |         1370 |
|   6827 |              70 |         1370 |
|   6829 |              62 |         1371 |
+--------+-----------------+--------------+

这是我到目前为止所经历的:

SELECT host.host_name, hostgroup.hg_name
FROM host, hostgroup_relation, hostgroup
WHERE hostgroup_relation.hostgroup_hg_id = hostgroup.hg_id
    AND hostgroup_relation.host_host_id = host.host_id
LIMIT 10;
+-----------+-----------------------------+
| host_name | hg_name                     |
+-----------+-----------------------------+
| AADSYNC1  | Default-bi                  |
| AADSYNC1  | Serveurs-Virtuels           |
| AADSYNC1  | Serveurs-Windows            |
| AADSYNC1  | Reboot_serveurs-12h00:14h00 |
| ADAUDIT1  | Default-bi                  |
| ADAUDIT1  | Serveurs-Virtuels           |
| ADAUDIT1  | Serveurs-Windows            |
| ADAUDIT1  | Reboot_serveurs-12h00:14h00 |
| ADMORA1   | Default-bi                  |
| ADMORA1   | Reboot_serveurs-00h00:4h00  |
+-----------+-----------------------------+

我需要一个不属于指定组的所有服务器的列表。

Answer 1

您需要进行简单的联接。

这可行:

SELECT h.host_id, h.host_name
FROM host h
LEFT OUTER JOIN hostgroup_relation hgr ON (hgr.host_host_id = h.host_id AND hgr.hostgroup_hg_id = 180)
WHERE hgr.hgr_id IS NULL