有一张表内容如下:
id,projectId,domainId
1, 1, 1
2, 1, 2
3, 1, 3
4, 2, 1
5, 2, 3
6, 3, 2
7, 3, 3
8, 3, 4
我想查询出domainId包含2,3的projectId
即结果:
projectId
1
3
id,projectId,domainId
1, 1, 1
2, 1, 2
3, 1, 3
4, 2, 1
5, 2, 3
6, 3, 2
7, 3, 3
8, 3, 4
我想查询出domainId包含2,3的projectId
即结果:
projectId
1
3
2 (
3 c1 number,
4 c2 number,
5 c3 number
6 )
7 ;Table createdSQL> insert into t2 values(1,1,1);1 row insertedSQL> insert into t2 values(2,1,2);1 row insertedSQL> insert into t2 values(3,1,3);1 row insertedSQL> insert into t2 values(4,2,1);1 row insertedSQL> insert into t2 values(5,2,3);1 row insertedSQL> insert into t2 values(6,3,2);1 row insertedSQL> insert into t2 values(7,3,3);1 row insertedSQL> insert into t2 values(8,3,4);1 row insertedSQL> commit;Commit completeSQL> select * from t2; C1 C2 C3
---------- ---------- ----------
1 1 1
2 1 2
3 1 3
4 2 1
5 2 3
6 3 2
7 3 3
8 3 48 rows selectedSQL> select c2 from t2 where c3=2; C2
----------
1
3SQL> select distinct c2 from t2 where c3 in(2,3); C2
----------
1
2
3SQL>
而我要的是包含2和3的。
ProjectId:2 他的 domainId(1,3)include(2,3)?????
(select distinct projectId from table where domainId = 2) a,
(select distinct projectId from table where domainId = 3) b
where a.projectId = b.projectId
(select projectId from table where domainId = 2 and projectId = a.projectId )
and a.domainId = 3
(select distinct projectid from table where domainid = 2 ) a,
(select distinct projectid from table where domainid = 3 ) b
where a.projectid = b.projectid
intersect
select distinct projectId from table where domainId=3;
union
select distinct projectid from table where domainid = 3
FROM [tabel]
WHERE domainId IN (2,3)
GROUP BY projectId
HAVING COUNT(DISTINCT domainId)=2
select projectid from Table where domainId IN (2,3))a
group by a.projectid没测过,应该可以
projectId in (Select projectId from tablename where domainid = 3)請看測試結果:
SQL> create table t2
2 (
3 c1 number,
4 c2 number,
5 c3 number
6 )
7 ;Table createdSQL> insert into t2 values(1,1,1);1 row insertedSQL> insert into t2 values(2,1,2);1 row insertedSQL> insert into t2 values(3,1,3);1 row insertedSQL> insert into t2 values(4,2,1);1 row insertedSQL> insert into t2 values(5,2,3);1 row insertedSQL> insert into t2 values(6,3,2);1 row insertedSQL> insert into t2 values(7,3,3);1 row insertedSQL> insert into t2 values(8,3,4);1 row insertedSQL> commit;Commit completeSQL>
SQL> Select distinct c2 from t2 where c3 = 2 and
2 c2 in (Select c2 from t2 where c3 = 3); C2
----------
1
3SQL>