给出的参考答案是c. select sname, phone from pizza, store, soldby where pname=”cheese” and soldby.pid=pizza.pid and soldby.sname=store.sname and soldby.sname in (select sname from pizza, soldby where pname=”veggie” and soldby.pid=pizza.pid) d. select sname from soldby minus (select sname from soldby, pizza where pname=”veggie” and soldby.pid=pizza.pid)
语句写法较多,但你写的C、D语句都是错误的。 C,不可能pid in (子查询) and pid in (子查询) D , 你的sname并没有排除那些既销售veggie,又销售其它产品的公司
不会吧,我两道题都错了 pid in改为pid =子查询??? 那D种参考答案就排除了你那种情况?
D难道答案就排除了你说的那种情况??? C.id in 可以(子查询)的 例子如下:select empno,ename,job from EMP where job in(select job from emp where deptno=30);
D参考答案好像也没有剪掉吧 select sname from soldby minus (select sname from soldby, pizza where pname=”veggie” and soldby.pid=pizza.pid)
select phone,sname from store s,soldby b where s.sname=b.sname and b.pid in (select pid from pizza p where p.sname=’veggie’) and b.pid in (select pid from pizza p where p.sname=’cheese’)现在in里的子查询,可以肯定的有且只有一个结果,其实这里的in 可以使用等号代替 对于一行记录而言,b.pid只有一个值 如果第一个in里的查询查询出来的是1,第二个in里查询出来的pid是2,你说对于b.pid只有一个值,怎么可能既要等于1又等于2呢?所以答案肯定是错误的。
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0 Connected as test SQL> SQL> --空说无凭,实战一下是最好的方式 SQL> create table pizza(pid varchar2(5),pname varchar2(50),psize number);Table createdSQL> create table store(sname varchar2(50),phone varchar2(20),quality varchar2(10));Table createdSQL> create table soldby(pid varchar2(5),sname varchar2(50),price number(6,2));Table createdSQL> insert into pizza values('01','veggie',10);1 row insertedSQL> insert into pizza values('02','cheese',12);1 row insertedSQL> insert into pizza values('03','other',8);1 row insertedSQL> insert into store values('store1','123245','1');1 row insertedSQL> insert into store values('store2','456789','2');1 row insertedSQL> insert into store values('store3','654321','3');1 row insertedSQL> insert into soldby values('01','store1',20);1 row insertedSQL> insert into soldby values('02','store1',20);1 row insertedSQL> insert into soldby values('01','store2',15);1 row insertedSQL> insert into soldby values('03','store2',25);1 row insertedSQL> commit;Commit completeSQL> select * from pizza;PID PNAME PSIZE ----- -------------------------------------------------- ---------- 01 veggie 10 02 cheese 12 03 other 8SQL> select * from store;SNAME PHONE QUALITY -------------------------------------------------- -------------------- ---------- store1 123245 1 store2 456789 2 store3 654321 3SQL> select * from soldby;PID SNAME PRICE ----- -------------------------------------------------- -------- 01 store1 20.00 02 store1 20.00 01 store2 15.00 03 store2 25.00SQL> --你的c答案(错误) SQL> --并且多个表中有相同列表时一定要用别名指名来源于那一个表 SQL> SELECT s.phone, s.sname 2 FROM store s, soldby b 3 WHERE s.sname = b.sname 4 AND b.pid IN (SELECT pid FROM pizza p WHERE p.pname = 'veggie') 5 AND b.pid IN (SELECT pid FROM pizza p WHERE p.pname = 'cheese');PHONE SNAME -------------------- --------------------------------------------------SQL> --你的d答案(错误) SQL> SELECT sname 2 FROM soldby 3 WHERE pid NOT IN (SELECT p.pid FROM pizza p WHERE p.pname = 'veggie');SNAME -------------------------------------------------- store1 store2SQL> --参考c答案(正确) SQL> SELECT s.sname,s.phone 2 FROM pizza, store s, soldby 3 WHERE pname = 'cheese' 4 AND soldby.pid = pizza.pid 5 AND soldby.sname = s.sname 6 AND soldby.sname IN (SELECT sname 7 FROM pizza, soldby 8 WHERE pname = 'veggie' 9 AND soldby.pid = pizza.pid);SNAME PHONE -------------------------------------------------- -------------------- store1 123245SQL> --参考d答案(错误) SQL> SELECT sname 2 FROM soldby--这儿如果改为store表就正确了 3 MINUS (SELECT sname 4 FROM soldby, pizza 5 WHERE pname = 'veggie' 6 AND soldby.pid = pizza.pid);SNAME --------------------------------------------------SQL> --c正确写法之一 SQL> SELECT s.sname, s.phone 2 FROM store s 3 WHERE s.sname IN 4 (SELECT b.sname 5 FROM soldby b 6 WHERE b.pid IN (SELECT p.pid 7 FROM pizza p 8 WHERE p.pname IN ('veggie', 'cheese')) 9 GROUP BY b.sname 10 HAVING COUNT(*) > 1);SNAME PHONE -------------------------------------------------- -------------------- store1 123245SQL> --d正确写法之一(使用exists) SQL> SELECT s.sname 2 FROM store s 3 WHERE NOT EXISTS 4 (SELECT 1 5 FROM soldby b 6 WHERE b.sname = s.sname 7 AND b.pid = (SELECT p.pid FROM pizza p WHERE p.pname = 'veggie'));SNAME -------------------------------------------------- store3SQL> --d正确写法之二(使用in) SQL> SELECT s.sname 2 FROM store s 3 WHERE s.sname NOT IN 4 (SELECT b.sname 5 FROM soldby b 6 WHERE b.pid = (SELECT p.pid FROM pizza p WHERE p.pname = 'veggie'));SNAME -------------------------------------------------- store3SQL>
嗯,我看错了。 c如果是这样写才是正确的 SQL> select phone,s.sname from store s 2 where s.sname in (select sname from pizza p,soldby b where p.pname='veggie' and p.pid=b.pid) 3 and s.sname in (select sname from pizza p,soldby b where p.pname='cheese' and p.pid=b.pid) ;
如果store3有卖东西,则下面的参考答案D选项是正确的吧--参考d答案(错误) SQL> SELECT sname 2 FROM soldby--这儿如果改为store表就正确了 3 MINUS (SELECT sname 4 FROM soldby, pizza 5 WHERE pname = 'veggie' 6 AND soldby.pid = pizza.pid);24楼好像假设的情况是store3没有卖东西
from pizza, store, soldby
where pname=”cheese” and soldby.pid=pizza.pid and soldby.sname=store.sname and soldby.sname in
(select sname
from pizza, soldby
where pname=”veggie” and soldby.pid=pizza.pid)
d. select sname
from soldby
minus
(select sname
from soldby, pizza
where pname=”veggie” and soldby.pid=pizza.pid)
C,不可能pid in (子查询) and pid in (子查询)
D , 你的sname并没有排除那些既销售veggie,又销售其它产品的公司
pid in改为pid =子查询???
那D种参考答案就排除了你那种情况?
C.id in 可以(子查询)的
例子如下:select empno,ename,job from EMP
where job in(select job from emp where deptno=30);
from soldby
minus
(select sname
from soldby, pizza
where pname=”veggie” and soldby.pid=pizza.pid)
只有当两个子查询查出的记录没有交集的时候才没有记录。d楼主的写法在逻辑上更贴近题意。
参考答案里的意思是如果那些既销售veggie,又销售其它产品的公司,也要减去吧
而我写的代码2楼说我没排除那些既销售veggie,又销售其它产品的公司??我自己都不知道我的代码有没有排除?如果没有,好像答案的更正确吧
minus或者子查询排除是一样的。
其实你可以自己写些测试数据测试下,你的写法得到的结果跟标准答案应该是一样的。
sql本来就是千变万化的,不是非得跟标准答案一致才是正确的。
where s.sname=b.sname and b.pid in (select pid from pizza p where p.sname=’veggie’)
and b.pid in (select pid from pizza p where p.sname=’cheese’)现在in里的子查询,可以肯定的有且只有一个结果,其实这里的in 可以使用等号代替
对于一行记录而言,b.pid只有一个值
如果第一个in里的查询查询出来的是1,第二个in里查询出来的pid是2,你说对于b.pid只有一个值,怎么可能既要等于1又等于2呢?所以答案肯定是错误的。
in是集合关系,=是单值比较,只有在集合中元素为1的时候两者才是等价的。
pid和pname才是一对一
Connected as test
SQL>
SQL> --空说无凭,实战一下是最好的方式
SQL> create table pizza(pid varchar2(5),pname varchar2(50),psize number);Table createdSQL> create table store(sname varchar2(50),phone varchar2(20),quality varchar2(10));Table createdSQL> create table soldby(pid varchar2(5),sname varchar2(50),price number(6,2));Table createdSQL> insert into pizza values('01','veggie',10);1 row insertedSQL> insert into pizza values('02','cheese',12);1 row insertedSQL> insert into pizza values('03','other',8);1 row insertedSQL> insert into store values('store1','123245','1');1 row insertedSQL> insert into store values('store2','456789','2');1 row insertedSQL> insert into store values('store3','654321','3');1 row insertedSQL> insert into soldby values('01','store1',20);1 row insertedSQL> insert into soldby values('02','store1',20);1 row insertedSQL> insert into soldby values('01','store2',15);1 row insertedSQL> insert into soldby values('03','store2',25);1 row insertedSQL> commit;Commit completeSQL> select * from pizza;PID PNAME PSIZE
----- -------------------------------------------------- ----------
01 veggie 10
02 cheese 12
03 other 8SQL> select * from store;SNAME PHONE QUALITY
-------------------------------------------------- -------------------- ----------
store1 123245 1
store2 456789 2
store3 654321 3SQL> select * from soldby;PID SNAME PRICE
----- -------------------------------------------------- --------
01 store1 20.00
02 store1 20.00
01 store2 15.00
03 store2 25.00SQL> --你的c答案(错误)
SQL> --并且多个表中有相同列表时一定要用别名指名来源于那一个表
SQL> SELECT s.phone, s.sname
2 FROM store s, soldby b
3 WHERE s.sname = b.sname
4 AND b.pid IN (SELECT pid FROM pizza p WHERE p.pname = 'veggie')
5 AND b.pid IN (SELECT pid FROM pizza p WHERE p.pname = 'cheese');PHONE SNAME
-------------------- --------------------------------------------------SQL> --你的d答案(错误)
SQL> SELECT sname
2 FROM soldby
3 WHERE pid NOT IN (SELECT p.pid FROM pizza p WHERE p.pname = 'veggie');SNAME
--------------------------------------------------
store1
store2SQL> --参考c答案(正确)
SQL> SELECT s.sname,s.phone
2 FROM pizza, store s, soldby
3 WHERE pname = 'cheese'
4 AND soldby.pid = pizza.pid
5 AND soldby.sname = s.sname
6 AND soldby.sname IN (SELECT sname
7 FROM pizza, soldby
8 WHERE pname = 'veggie'
9 AND soldby.pid = pizza.pid);SNAME PHONE
-------------------------------------------------- --------------------
store1 123245SQL> --参考d答案(错误)
SQL> SELECT sname
2 FROM soldby--这儿如果改为store表就正确了
3 MINUS (SELECT sname
4 FROM soldby, pizza
5 WHERE pname = 'veggie'
6 AND soldby.pid = pizza.pid);SNAME
--------------------------------------------------SQL> --c正确写法之一
SQL> SELECT s.sname, s.phone
2 FROM store s
3 WHERE s.sname IN
4 (SELECT b.sname
5 FROM soldby b
6 WHERE b.pid IN (SELECT p.pid
7 FROM pizza p
8 WHERE p.pname IN ('veggie', 'cheese'))
9 GROUP BY b.sname
10 HAVING COUNT(*) > 1);SNAME PHONE
-------------------------------------------------- --------------------
store1 123245SQL> --d正确写法之一(使用exists)
SQL> SELECT s.sname
2 FROM store s
3 WHERE NOT EXISTS
4 (SELECT 1
5 FROM soldby b
6 WHERE b.sname = s.sname
7 AND b.pid = (SELECT p.pid FROM pizza p WHERE p.pname = 'veggie'));SNAME
--------------------------------------------------
store3SQL> --d正确写法之二(使用in)
SQL> SELECT s.sname
2 FROM store s
3 WHERE s.sname NOT IN
4 (SELECT b.sname
5 FROM soldby b
6 WHERE b.pid = (SELECT p.pid FROM pizza p WHERE p.pname = 'veggie'));SNAME
--------------------------------------------------
store3SQL>
c如果是这样写才是正确的
SQL> select phone,s.sname from store s
2 where s.sname in (select sname from pizza p,soldby b where p.pname='veggie' and p.pid=b.pid)
3 and s.sname in (select sname from pizza p,soldby b where p.pname='cheese' and p.pid=b.pid) ;
PHONE SNAME
-------------------- --------------------------------------------------
123245 store1
SQL>
SQL> select sname
2 from store
3 where sname not in (select s.sname
4 from pizza p, soldby s
5 where p.pname = 'veggie'
6 and p.pid = s.pid);
SNAME
--------------------------------------------------
store3
SQL>
SQL> SELECT sname
2 FROM soldby--这儿如果改为store表就正确了
3 MINUS (SELECT sname
4 FROM soldby, pizza
5 WHERE pname = 'veggie'
6 AND soldby.pid = pizza.pid);24楼好像假设的情况是store3没有卖东西