表里有这样的数据
ID TIMES
1 2
1 2
1 0
1 3
2 2
2 2
2 2
希望查询到当id相同的记录中times全都为2的 id的列表
也就是说,执行sql后,希望得到的结果:
ID
2
答案:
ID TIMES
1 2
1 2
1 0
1 3
2 2
2 2
2 2
希望查询到当id相同的记录中times全都为2的 id的列表
也就是说,执行sql后,希望得到的结果:
ID
2
答案:
SELECT ID
FROM TEST
WHERE ID IN (SELECT ID FROM TEST WHERE TIMES = 2)
GROUP BY ID
HAVING COUNT(DISTINCT TIMES) = 1
select id from table group by id having count(DISTINCT times)=1 and avg(times)=2
select id
from (select id, max(times) ma, min(times) mi
from TEMP_TABLE t
group by id)
where ma = 2
and mi = 2
select s1.id from test s1 where not exists (
select 1 from test s2 where s2.id = s1.id and s2.times<>2
) group by s1.id