mysql - 查询在sql中不使用 'Not IN'

Question

如何编写以下不使用 Not IN 的查询。由于性能原因IN、Not in均不可取。

select d.cert_serial_number as str from mdm_device d where d.client_id in (:CLIENT_IDS) and d.cert_serial_number not in (Select cert_serial_number from mdm_device where client_id not in (:CLIENT_IDS))

感兴趣的表格列:

| cert_serial_number | client_id  |
+--------------------+------------+
|                102 | 1073741835 | 
|                102 | 1073741836 | 
|                102 | 1073741837 |
|                102 | 1073741838 | 
|                102 | 1073741839 | 
|                103 | 1073741840 | 
|                103 | 1073741831 | 
+--------------------+------------+------------+

如果客户端 ID 输入为 1073741835，1073741836 输出应为空。
如果客户端 ID 输入为 1073741835,1073741836,1073741837,1073741838,1073741839 输出应为 102。

编辑:我最终使用了以下查询:SELECT d.cert_serial_number AS str FROM mdm_device d where d.client_id in (:CLIENT_IDS) AND NOT EXISTS (SELECT 1 FROM mdm_device e WHERE d.cert_serial_number = e.cert_serial_number AND d.client_id != e.client_id)

Answer 1

假设您的查询是正确的，您可以使用不存在:

select d.cert_serial_number as str
from mdm_device d
where d.client_id in (:CLIENT_IDS) and
      not exists (select 1
                  from mdm_device d2
                  where d2.cert_serial_number = d.cert_serial_number and
                        d2.client_id not in (:CLIENT_IDS)
                 );

或者您可以使用聚合:

select d.cert_serial_number as str
from mdm_device d
group by d.cert_serial_number
having sum(d2.client_id in (:CLIENT_IDS)) = count(*);

也就是说，仅返回具有这些序列号的设备，而不返回其他序列号。