过滤mysql中多个列和所有重复行的id值为0

Question

我有一个如下所示的表格:

原始数据

我希望对数据进行子集，以便在多个列中留下具有0值的id，包括具有多个行的id的所有重复行。

目标数据我试过了

select id, group, year, var1, var2, var3, var4 from tbl where var1 = 0 and var2 = 0  and var3 = 0  and var4 = 0;

这是给我的第一个记录id 92403(在上面的示例中),我不希望因为这个id有非零值的其他记录。

Answer 1

这里的一种方法是使用聚合方法来查找所有4列中记录组都为零的所有id值:

WITH cte AS (
SELECT id, `group`
FROM tbl
GROUP BY id, `group`
HAVING SUM(var1 = 0) = COUNT(*) AND
SUM(var2 = 0) = COUNT(*) AND
SUM(var3 = 0) = COUNT(*) AND
SUM(var4 = 0) = COUNT(*)
)
SELECT *
FROM tbl1
WHERE (id, `group`) IN (SELECT id, `group` FROM cte);

Answer 2

select 
t1.id, t1.group, t1.year, t1.var1, t1.var2, t1.var3, t1.var4
from
table as t1
inner join
(select
id
from
table
group by id having count(1) = 1) as t2 on (t1.id = t2.id)
where
t1.var1 = 0 and t1.var2 = 0  and t1.var3 = 0 and t1.var4 = 0;