现在我有一个表，
SNO PNO JNO
S1 P1 J1
S1 P2 J3
S2 P2 J1
S3 P3 J1


我想查询至少包含所有S1的PNO也就是（包含P1,P2）
的JNO在这里应该是J1吧。

这个用SQL语句应该怎么写呢？

该题难度有点大
望大家思考思考、

select distinct pno
from table a
where not exists
(select *
from table b
where b.sno='s1' and not exists
(select *
from table c
where c.jno=a.jno and b.pno=c.pno)
如果大家还是不懂的话,看一下数据库原理那本书.

不是太明白你说的意思!!
说清楚嘛!!
select A.SNO from A where A.PNO=P1 and A.PNO=P2

下面这段语句好像是答案，两位版主可以帮我解释一下吗？小沙初学数据库，很多看不懂啊。

假设表名 tab1

select jno from
(SELECT pno,JNO from
tab1 t1 where pno in
(select pno from tab1 where sno = 'S1')
)T2 group by jno having count(JNO) = (select count(1) from tab1 where sno = 'S1')

感觉volte版主的语句比小沙上面所写的要好，尽管这样，小沙还是希望大家帮我分析一下，上面那个语句

是什么意思。

其实小沙还是不是很懂volte版主的那段语句的意思，可以说说思路嘛？

现在我有一个表，
SNO PNO JNO
S1 P1 J1
S1 P2 J3
S2 P2 J1
S3 P3 J1

是不是这样理解：
求JNO，每个相同JNO对应的所有不同的PNO等于或者多于S1对应的所有PNO。

declare @s1 integer,/*定义S1对应的PNO的个数*/
set @s1=(select count(pno) from table where sno='s1' group by pno) ,/*求S1对应的PNO的个数，去除重复的PNO*/

select jno from table
where pno in
(select pno from table where sno='s1')
GRUOP BY JNO,PNO/*去除重复的PNO,jno*/

HAVING COUNT(JNO)>=@S1 /*每个JNO对应的不同的PNO的数量应该大于等于@s1*/

select jno from
(SELECT pno,JNO from
tab1 t1 where pno in
(select pno from tab1 where sno = 'S1')
)T2 group by jno having count(JNO) = (select count(1) from tab1 where sno = 'S1')

其中:
SELECT pno,JNO from
tab1 t1 where pno in
(select pno from tab1 where sno = 'S1')
结果 t2 是
pno JNO
p1 j1
p2 j3
p2 j1

select count(1) from tab1 where sno = 'S1'
结果为
2
语句简化为
select jno
from t2
group by jno
having count(jno)=2

结果为
jno
j1

Bilicon对问题表述不清。

以下是引用lili在2005-11-11 14:24:25的发言：
select jno from
(SELECT pno,JNO from
tab1 t1 where pno in
(select pno from tab1 where sno = 'S1')
)T2 group by jno having count(JNO) = (select count(1) from tab1 where sno = 'S1')

其中:
SELECT pno,JNO from
tab1 t1 where pno in
(select pno from tab1 where sno = 'S1')
结果 t2 是
pno JNO
p1 j1
p2 j3
p2 j1

select count(1) from tab1 where sno = 'S1'
结果为
2
语句简化为
select jno
from t2
group by jno
having count(jno)=2

结果为
jno
j1

Bilicon对问题表述不清。

我想请教一下“group by jno having count(JNO) = (select count(1) from tab1 where sno = 'S1')”是不是应该用：
group by jno having count(JNO) >= (select count(1) from tab1 where sno = 'S1')
因为按照楼主所说是“至少包含”的意思。