sql问题，不要复制网上的

来源：百度知道 编辑：UC知道 时间：2024/05/20 16:37:56
现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：

商品product（商品号productid，商品名productname，单价unitprice，商品类别category，供应商provider）；

顾客customer（顾客号customerid，姓名name，住址location）；

购买purcase（顾客号customerid，商品号productid，购买数量quantity）；
问题：求购买的商品包含了顾客"Dennis"所购买的所有商品的顾客（姓名）；
请不要复制网上的，要求详细解释
二楼的朋友，好像不对啊！
因为是要求dennis的所购买的所有商品啊

select (select name from customer where customerid=c.customerid) from (
select customerid from purcase where productid in (select productid from (select customerid,customerid,(select name from customer where customerid=a.customerid) from purcase a) where name='Dennis' group by productid) d group by customerid) c

====================================
解释如下：
1。select customerid,customerid,(select name from customer where customerid=a.customerid) from purcase a //取所有购买的商品的信息，并通过顾客ID得到顾客的姓名
2。select productid from (select customerid,customerid,(select name from customer where customerid=a.customerid) from purcase a) where name='Dennis' group by productid //根据1得到的信息取出顾客名字为‘Dennis’所购买的所有商品的ID集合
3。select customerid from purcase where productid in (select productid from (select customerid,customerid,(select name from customer where customerid=a.customerid) from purcase a) where name='Dennis' group by productid) d group by customerid