有些记录带着一些条件，我是数不清的。 例如，我有这张桌子
+-----+-----------------+-------+
| CCA | NUMERO | STATO |
+-----+-----------------+-------+
| 057 | 007030020004527 | 0 |
| 057 | 007030020004527 | 1 |
| 057 | 007030020004527 | 1 |
| 057 | 007030020004123 | 1 |
| 057 | 007030020004123 | 1 |
| 057 | 007030020001111 | 1 |
| 057 | 007030020001111 | 1 |
| 057 | 007030020001111 | 1 |
+-----+-----------------+-------+
我对本例的预期结果是
057 2
当count(NUMERO)=SUM（STATO）时，我想为每个CCA计数NUMERO的数目，但我不能。 我尝试了类似的操作，但结果不正确（表名为sinistro）
SELECT cca, count(numero) AS totali, sum(stato) as gestiti
FROM `sinistro`
GROUP BY sinistro.cca
HAVING (totali - gestiti) = 0
我可以列出所有有这个条件的数字，但我不能数。 我总是得不到行。
我怎么能这么做呢？
您需要两个级别的聚合:
select t.cca, count(*) counter
from (
select cca, numero
from tablename
group by cca, numero
having count(*) = sum(stato) -- or having min(stato) = 1
) t
group by t.cca
请参阅演示。
或带有不存在:
select cca, count(distinct numero) counter
from tablename t
where not exists (
select 1
from tablename
where cca = t.cca and numero = t.numero and stato = 0
)
group by cca
请参阅演示。
结果:
| cca | counter |
| --- | ------- |
| 57 | 2 |
你似乎想:
select cca
from sinistro
group by cca
having count(*) = sum(stato);
