日期:2014-05-17  浏览次数:21264 次

求一个字段同时等于两个值得查询语句
查询一个字段同时等于两个值得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 的值只能是两个值,那位高手能指点一下怎么写



------解决方案--------------------
select id from xxx
where trunc(OPERATOR_DATE)=trunc(sysdate-1))
group by id,m
having count(m)=2
------解决方案--------------------
select distinct id
from table1
where m = 3 and id in(select id 
from table1
where m =4);
------解决方案--------------------
至今为止想到的一个"很傻很天真"方法: :)

SQL code

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 ID1

SQL>