在某些情况下,我无法计算某些记录。我有这个桌子
+-----+-----------------+-------+
| 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
我想为每个CCA计算COUNT(NUMERO)= SUM(STATO)时的NUMERO数量,但是我不能。我尝试过类似的操作,但结果不正确(表名称为sinistro)
SELECT cca, count(numero) AS totali, sum(stato) as gestiti
FROM `sinistro`
GROUP BY sinistro.cca
HAVING (totali - gestiti) = 0
我可以列出所有有这种情况的数字,但是我无法计数。我总是没有行。
我怎样才能做到这一点?
您需要2级聚合:
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
参见演示。
或搭配NOT EXISTS
:
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 |
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句