查询一个字段同时等于两个值得sql语句
比如:
id n m
id1 2 3
id1 2 4
id2 3 3
id3 3 5
求m=3并且m=4 的这个id1,select id from
(select id ,count(m) cid from
(select distinct id,m from xxx where trunc(OPERATOR_DATE)=trunc(sysdate-1)) group by id ) where cid=2这个语句局限在m 的值只能是两个值,那位高手能指点一下怎么写
比如:
id n m
id1 2 3
id1 2 4
id2 3 3
id3 3 5
求m=3并且m=4 的这个id1,select id from
(select id ,count(m) cid from
(select distinct id,m from xxx where trunc(OPERATOR_DATE)=trunc(sysdate-1)) group by id ) where cid=2这个语句局限在m 的值只能是两个值,那位高手能指点一下怎么写
where trunc(OPERATOR_DATE)=trunc(sysdate-1))
group by id,m
having count(m)=2
where trunc(OPERATOR_DATE)=trunc(sysdate-1))
group by id
having count(m)=2
FROM (SELECT gc_name, COUNT (hd_type) cid
FROM (SELECT DISTINCT gc_name, hd_type
FROM zb_wenhua_hdtj_day
WHERE TRUNC (operator_date) = TRUNC (SYSDATE - 1)
AND hd_type IN ('1', '3'))
GROUP BY gc_name)
WHERE cid = '2'
我刚才问的别人,这样可以
from table1
where m = 3 and id in(select id
from table1
where m =4);
SQL> SELECT T1.SID,
2 T2.SID
3 FROM XXX T1,
4 XXX T2
5 WHERE T1.SID = T2.SID
6 AND T1.M = 3
7 AND T2.M = 4;SID SID
--- ---
ID1 ID1SQL>
id n m
id1 2 3
id1 2 4
id1的 m的值
select id from xxx
where trunc(OPERATOR_DATE)=trunc(sysdate-1))
group by id
having count(m)=2
如果就是要m=3和4的行对应的id,那么oracle有intersect,你可以查查
(select id from xxx
where m ='3'
group by id
)
intersect
(
select id from xxx
where m ='4'
group by id)
或者
select a.id from xxxx a,xxx b
where a.id=b.id and
a.m=3 and b.m=4
group by a.id或者
select id
from xxx
where m=3 and
exists(select id from xxxx b where b.id= xxx.id and b.m=4)
group by id或者(不推荐,执行效率低)
select id from xxx
where m=3 and id in(select id from xxxx b where b.id= xxx.id and b.m=4)