表里有这样的数据
ID TIMES
1 2
1 2
1 0
1 3
2 2
2 2
2 2希望查询到当id相同的记录中times全都为2的 id的列表
也就是说,执行sql后,希望得到的结果:
ID
2不知道该怎么写SQL?
ID TIMES
1 2
1 2
1 0
1 3
2 2
2 2
2 2希望查询到当id相同的记录中times全都为2的 id的列表
也就是说,执行sql后,希望得到的结果:
ID
2不知道该怎么写SQL?
FROM TEST
WHERE ID IN (SELECT ID FROM TEST WHERE TIMES = 2)
GROUP BY ID
HAVING COUNT(DISTINCT TIMES) = 1试试这样行不行
select distinct a.id
from table_name a,table_name b
where a.id = b.id
and a.time = b.time
and a.time = 2;
这个改下也许可以。
select distinct a.id
from table_name a,table_name b
where a.id = b.id
and a.id = b.time 最好不要出现time = 2这种,数据一变立刻就查不出了。
-----table & data--------------
create table t2(
id number ,
time number
)insert into t2 values(1,2)
insert into t2 values(1,2)
insert into t2 values(1,0)
insert into t2 values(1,3)
insert into t2 values(2,2)
insert into t2 values(2,2)
insert into t2 values(2,2)
-------- sql statement--------------
select decode(avg(time),2,id) id from t2 group by id
-------result---------
1
2 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
decode(avg,2,id) id from (
select id,avg(time) avg from t2 group by id ) where avg = 2
select 1 from s s2 where s2.id = s1.id and s2.times<>2
) group by s1.id
运行报错
[Err] 1582 - Incorrect parameter count in the call to native function 'DECODE'
1 SELECT ID
FROM TEST
WHERE ID IN (SELECT ID FROM TEST WHERE TIMES = 2)
GROUP BY ID
HAVING COUNT(DISTINCT TIMES) = 1 2select id from table group by id having count(DISTINCT times)=1 and avg(times)=2
3select id
from (select id, max(times) ma, min(times) mi
from TEMP_TABLE t
group by id)
where ma = 2
and mi = 2 4select 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 这几个都行,我用了第二个
方法一
select id from a
minus
select id from a where times !=2 方法二
select id
from a
group by id
having count(id) = sum(decode(times,2,1,0))
FROM TEST
WHERE ID IN (SELECT ID FROM TEST WHERE TIMES = 2)
GROUP BY ID
HAVING COUNT(DISTINCT TIMES) = 1