请教，这样的查询怎样写？

[gotoyangjm]

表A：
No Brand
1 DELL
2 DELL
3 IBM
4 HP

表B：
No Item Des
1 CPU P4
1 Disk 40G
1 CDROM 48X
2 CPU P3
2 Disk 30G
3 CPU P4
3 Disk 40G
4 CPU P4
4 Disk 60G

说明：表A是记录电脑的信息，表B是对应表A电脑的配置信息，从上表知道，编号为1的电脑：品牌是DELL，CPU是P4，硬盘是40G，CDROM是52速的。

查询1，查询所有符合CPU为P4的电脑。
查询2，查询所有符合CPU为P4，硬盘为40G的电脑。

输出结果为表A的结构，即：
查询1结果：
No Brand
1 DELL
3 IBM
4 HP

查询2结果：
No Brand
1 DELL
3 IBM

请问：以上的查询1和查询2对应的SQL语句怎么写？

[freelove1]

1. Select A.no,A.Brand from A a, (Select distinct b.no from B b
Where b.Item="CPU" and b.Des="P4" ) b

Where a.no=b.no

2. Select A.no,A.Brand from A a,
(Select distinct b.no from B b
Where b.Item="CPU" and b.Des="P4" ) b,
(Select distinct b.no from B b
Where b.Item="Disk" and b.Des="40G" ) c

Where a.no=b.no and a.no=c.no


Where a.no=b.no

[freelove1]

1. Select A.no,A.Brand from A a, (Select distinct b.no from B b
Where b.Item="CPU" and b.Des="P4" ) b

Where a.no=b.no

2. Select A.no,A.Brand from A a,
(Select distinct b.no from B b
Where b.Item="CPU" and b.Des="P4" ) b,
(Select distinct b.no from B b
Where b.Item="Disk" and b.Des="40G" ) c

Where a.no=b.no and a.no=c.no


后面的那個Where a.no=b.no 是多餘的

[gotoyangjm]

to freelove1() :
以下语句与你的比，哪一个效率高？
select distinct a.no,a.brand
from a,b
where a.no = b.no and
a.no in (select b.no from b
where b.des = 'P4') and
a.no in (select b.no from b
where b.des = '40G')

[freelove1]

你的那個高,但如果你select 出來的字段多,而且結果集大的話我的效率高

[96163]

select no,brank from 表A where no in (select distinct no from 表B where des='P4')

select a.no,a.brand from 表b b1,表b b2,表a as a where b1.item='cpu' and b1.des=
'p4' and b2.item='disk' and b2.des='40g' and b1.no=b2.no and b1.no = a.no and b2
.no = a.no