订单表 orders(cid,ordernum,...) cid 客户编号;ordernum 订单编号订单明细表 orderlist(ordernum,bid,...) ordernum 订单编号;bid 图书编号一个客户可以对应若干个订单;一张订单有若干个订单明细,一张订单图书编号bid不重复出现。现在要求查询“所有订购了bid为‘123-456’图书的用户订购其他图书的情况”他给出的答案为SELECT bid
FROM orderlist A
WHERE (NOT EXISTS
(SELECT *
FROM orders B
WHERE A.ordernum = B.ordernum AND B.cid NOT IN
(SELECT cid
FROM orderlist C, orders D
WHERE C.bid = '123-456' AND C.ordernum = D.ordernum)))我觉得'123-456'这个图书也包含在里面啊。
FROM orderlist A,orders B
where A.ordernum = B.ordernum
and B.cid in (SELECT cid FROM orderlist a, orders b WHERE a.bid = '123-456' AND a.ordernum = b.ordernum)
and bid not in('123-456')
orders a
inner join
orderlist b
on b.ordernum=a.ordernum
and b.bid<>'123-456'
and
a.cid in (
select distinct cid from orders
where ordernum in
(select ordernum from orderlist where bid='123-456')
)
这样也可以写啊 答案很多的 软考不灵