sql - 如果多次找到一个字段，如何不获取数据

Question

我从安装和设备编号的查询中获得结果，但是如果我在安装中有多个设备，我想省略该数据。

select a~anlage, c~equnr
from eastl as a 
inner join egerh as b on a~logiknr = b~logiknr and a~bis = '99991231' and b~bis = '99991231'
inner join equi as c on b~equnr = c~equnr and c~matnr = 'REGULATOR'
group by a~anlage, c~equnr 
having count(*) = 1

---

我目前的结果如下所示：

anlage - equnr
111111 - 01012
111111 - 01013
111111 - 01014
222222 - 01015
222222 - 01016
444444 - 01017

---

我希望这样：

anlage - equnr
444444 - 01017

标签： sqlcds

Answer 1

看来您只需要按以下方式分组a~anlage：

select a~anlage, max(c~equnr)
from eastl as a 
inner join egerh as b on a~logiknr = b~logiknr and a~bis = '99991231' and b~bis = '99991231'
inner join equi as c on b~equnr = c~equnr and c~matnr = 'REGULATOR'
group by a~anlage
having count(*) = 1