Считать и группировать с исключением?

Даны следующие упрощенные таблицы:

--- Incident ---
ID | ID_NUMBER | AREA

--- Vehicle ---
ID | NAME | INCIDENT_ID

Как я могу посчитать количество инцидентов, сгруппированных по области, но только если транспортные средства, связанные с инцидентом, не имеют названия, такого как M<any number><any number> или D<any number> ?

У меня как то так, но цифры кажутся большими:

select count(i.ID_NUMBER), i.AREA
from Incident i
inner join Vehicle v on v.INCIDENT_ID = i.ID
where v.NAME not like 'M[0-9][0-9]'
and v.NAME not like 'D[0-9]
group by i.AREA

Я думаю, что цифры слишком велики, потому что он учитывает каждое транспортное средство на каждый инцидент, но я просто хочу посчитать количество Incident.ID котором не указано конкретное «Vehicle.NAME».

Чтобы расширить пример, приведем следующее:

---Incident---
1 | 1234 | Here
2 | 9876 | There
3 | 4567 | Here

---Vehicle---
1 | R09 | 1234
2 | F99 | 1234
3 | Q23 | 4567
4 | Y78 | 9876
5 | M42 | 9876

Результаты будут

2 | Here

sql sql-server tsql

Всего 2 ответа

Использование not exists вместо этого:

select count(i.ID_NUMBER), i.AREA
from Incident i
where not exists (select 1
                  from vehicle v
                  where v.INCIDENT_ID = i.ID and
                        (v.NAME like 'M[0-9][0-9]' or v.NAME like 'D[0-9]')
group by i.AREA;

Это должно удалить дубликаты перед агрегацией, что также должно повысить производительность.

Вы должны посчитать отдельные инциденты и вместо WHERE использовать предложение HAVING :

select count(distinct i.ID_NUMBER) counter, i.AREA
from Incident i
inner join Vehicle v on v.INCIDENT_ID = i.ID_NUMBER
group by i.AREA
having sum(case when v.NAME like 'M[0-9][0-9]' or v.NAME like 'D[0-9]' then 1 else 0 end) = 0

Смотрите демо .
Результаты:

> counter | AREA
> ------: | :---
>       2 | Here