写sql遇到难题了

[coolice_2004]

有一张表内容如下：
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

[Renkey]

select a.projectid from
(select distinct projectid from table where domainid = 2 ) a,
(select distinct projectid from table where domainid = 3 ) b
where a.projectid = b.projectid

[cenlmmx]

select distinct projectId from table where domainId in (2,3)

[zhpsam109]

domainId包含2，3的projectId应改还有2啊，结果怎么会是1,3?？？这一行呢：5, 2, 3

[zhpsam109]

SQL> create table t2
2 (
3 c1 number,
4 c2 number,
5 c3 number
6 )
7 ;

Table created

SQL> insert into t2 values(1,1,1);

1 row inserted

SQL> insert into t2 values(2,1,2);

1 row inserted

SQL> insert into t2 values(3,1,3);

1 row inserted

SQL> insert into t2 values(4,2,1);

1 row inserted

SQL> insert into t2 values(5,2,3);

1 row inserted

SQL> insert into t2 values(6,3,2);

1 row inserted

SQL> insert into t2 values(7,3,3);

1 row inserted

SQL> insert into t2 values(8,3,4);

1 row inserted

SQL> commit;

Commit complete

SQL> 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 4

8 rows selected

SQL> select c2 from t2 where c3=2;

C2
----------
1
3

SQL> select distinct c2 from t2 where c3 in(2,3);

C2
----------
1
2
3

SQL>

[coolice_2004]

projectId为2的domainId是1和3
而我要的是包含2和3的。
ProjectId：2 他的 domainId（1，3）include（2，3）？？？？？

[6liuy]

select a.projectId from
(select distinct projectId from table where domainId = 2) a,
(select distinct projectId from table where domainId = 3) b
where a.projectId = b.projectId

[6liuy]

select distinct projectId from table a where exists
(select projectId from table where domainId = 2 and projectId = a.projectId )
and a.domainId = 3

[boydgmx]

select distinct projectId from table where domainId=2
intersect
select distinct projectId from table where domainId=3;

[guangli_zhang]

题是不是有问题

[Renkey]

select distinct projectid from table where domainid = 2
union
select distinct projectid from table where domainid = 3

[bugchen888]

SELECT projectId
FROM [tabel]
WHERE domainId IN (2,3)
GROUP BY projectId
HAVING COUNT(DISTINCT domainId)=2

[MerlinSky]

select a.projectid from(
select projectid from Table where domainId IN (2,3))a
group by a.projectid

没测过，应该可以

[tommysun]

這個好辦啊，我用樓上的數據測試了一下，可以用的

Select distinct projectId from tablename where domainid = 2 and
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 created

SQL> insert into t2 values(1,1,1);

1 row inserted

SQL> insert into t2 values(2,1,2);

1 row inserted

SQL> insert into t2 values(3,1,3);

1 row inserted

SQL> insert into t2 values(4,2,1);

1 row inserted

SQL> insert into t2 values(5,2,3);

1 row inserted

SQL> insert into t2 values(6,3,2);

1 row inserted

SQL> insert into t2 values(7,3,3);

1 row inserted

SQL> insert into t2 values(8,3,4);

1 row inserted

SQL> commit;

Commit complete

SQL>
SQL> Select distinct c2 from t2 where c3 = 2 and
2 c2 in (Select c2 from t2 where c3 = 3);

C2
----------
1
3

SQL>