SQL面试题

#求销售额最少的前三种商品名称及成交总金额
SELECT p.name,IF(SUM(priceAMOUNT) is NULL,0,SUM(priceAMOUNT)) ‘总价’ FROM goods g RIGHT JOIN product p ON g.ID = p.id GROUP BY name ORDER BY SUM(price*AMOUNT) ASC LIMIT 3

IF(SUM(priceAMOUNT) is NULL,0,SUM(priceAMOUNT)) 如果字段为NULL可以使用此代码设置为零

ORDER BY SUM(price*AMOUNT) ASC LIMIT 3 对该字段进行降序排序 LIMIT3 是获取前三个

RIGHT JOIN 右外链接 ：以右表为主，主要是查询出右表中不符合条件的数据

#每个顾客 在购买 水果和 家电上分别 花了多少钱（一个顾客一行）
SELECT ACNAME ‘姓名’,ACADRESS ‘城市’,SUM(price*AMOUNT) ‘总价’,type ‘类型’ FROM customer c,goods g ,product p where c.ACID = g.ACID and g.ID = p.id GROUP BY ACNAME,type,ACADRESS

#求没有买过李子的顾客名称及地址
SELECT ACNAME ‘姓名’,ACADRESS ‘城市’ from customer c,goods g,product p WHERE c.ACID = g.ACID and g.ID = p.id and c.ACNAME != (SELECT ACNAME from customer c,goods g,product p WHERE c.ACID = g.ACID and g.ID = p.id and p.name = ‘李子’) GROUP BY ACNAME,ACADRESS