客户表(buyid用户ID buyname用户名自)
订单表(proid产品ID buyid用户ID num数量)
产品表(proid产品ID proname产品名字)1.求客户是"张三"买的产品ID和名称。
2.求买了产品“桌子”的客户ID和名称。
3.求买产品“桌子”最多的前三位
订单表(proid产品ID buyid用户ID num数量)
产品表(proid产品ID proname产品名字)1.求客户是"张三"买的产品ID和名称。
2.求买了产品“桌子”的客户ID和名称。
3.求买产品“桌子”最多的前三位
SELECT proid, proname
FROM 订单表
INNER JOIN 客户表 using(buyid)
INNER JOIN 产品表 USING(proid)
WHERE buyid = '张三';2.
SELECT buyid, buyname
FROM 订单表
INNER JOIN 客户表 using(buyid)
INNER JOIN 产品表 USING(proid)
WHERE 产品表.proname = '桌子';
select p.proid, p.proname from product p, order o, client c
where p.proid = o.proid and o.buyid = c.buyid
and c.buyname = '张三';
SELECT buyid, buyname
FROM
(SELECT buyid, buyname, sum(NUM), ROWNUM AS n
FROM 订单表
INNER JOIN 客户表 using(buyid)
INNER JOIN 产品表 USING(proid)
WHERE 产品表.proname = '桌子'
GROUP BY buyid, buyname
ORDER BY SUM(NUM) DESC)
WHERE n <= 3;
FROM 订单表
INNER JOIN 客户表 using(buyid)
INNER JOIN 产品表 USING(proid)
WHERE buyname = '张三';