选择时，异常情况计数=0，寄存器=1

Question

工作正常：

SELECT  m.*, i.description
FROM mac_address_gt m
INNER JOIN int_gt i ON m.ip = i.ip AND m.interface = i.interface
WHERE m.mac = ? AND m.count > 0 AND m.vlan = ?
ORDER BY m.count asc

但有一种特殊情况，如果m.count=0，返回的寄存器数=1，我必须显示它

Answer 1

目前还不清楚您的情况下寄存器的数量，因此这个答案将只关注如何更改查询。可能产生的查询可能看起来像

SELECT  m.*, i.description
FROM mac_address_gt m
INNER JOIN int_gt i ON m.ip = i.ip AND m.interface = i.interface
WHERE m.mac = ? AND ((m.count > 0) OR ((m.count) AND (<number of returned registers criteria>))) AND m.vlan = ?
ORDER BY m.count asc

<number of returned registers criteria>由您来执行。此外，如果返回的寄存器数量恰好是m.vlan或m.mac，则需要重新构造查询。

编辑

根据注释部分给出的查询，这就是如何重写查询：

SELECT m.*, i.description 
FROM mac_address_hn m 
INNER JOIN int_hn i 
ON m.ip = i.ip AND 
m.interface = i.interface 
JOIN (SELECT COUNT(m.count) as cnt FROM mac_address_hn m INNER JOIN int_hn i ON m.ip = i.ip AND m.interface = i.interface WHERE m.mac = "78d3.47a3.5b78" AND m.vlan = "4010") mycount
ON 1=1
WHERE  m.mac = "78d3.47a3.5b78" AND 
m.vlan = "4010" AND 
(((count = 0) and (mycount.cnt = 1)) or
((count > 0) and (mycount.cnt > 0))
);