MySQL多表查询简单理解（查询升级版）

要求
（1）按公司客户所在地来统计销售总额
（2）查询公司从96年8月开始单价超过2000块的采购单号和供应商
（3）查询公司目前库存商品的名称和数量
（4）查询公司采购金额超过10万的定单信息（包括定单号厂家商品名）
（5）查询库存量前三名的产品名称
（6）查询库存商品销售金额在第三到第六的供货商的信息
（7）查询同一类型产品有两家以上供货商的产品编号以及供货商的数量
（8）统计公司各种产品的销售金额（需要区分不同的厂家）
（9）查询公司在96年10月的定单，计算每日定单金额，并按照定单金额排序
（10）查询一笔销售记录中包含有两条明细记录的销售总帐记录
（11）查询销售总表和销售明细表中不符合参照关系的数据（定单编号为参照字段）。
（12）查询每个员工的工资以及应该交纳的个人所得税金额（40000以下不交， 40000---49999 5% 50000—59999 7% 60000以上 10%）
（13）生成公司销售的明细表要求表中需要表现的信息为（定单号，销售员姓名，销售产品，供伙商名称，销售金额）
（14）在采购明细表中查询同类产品在不同时间进货差价超过200元的产品及供货商名称
（15）查询在同一天进入公司的员工信息
（16）查询公司所有客户在公司的定货情况
（17）查询由公司女业务员所接回的定单
（18）查询公司中姓名相同的员工并按照员工编号显示员工信息
（19）查询公司中目前业绩还没有超过2万的业务员
（20）查询仓库中还没有销售过的产品信息
（21）查询没有在公司订购产品的客户名单
（22）按照供货商来统计公司的销售榜

过程
（1）按公司客户所在地来统计销售总额
这里我使用了两个表一个是customer,一个是sales表
SELECT addr as 所在地,sum(toa_amt) as 销售总额 FROM customer,sales
WHERE customer.cus_id=sales.cus_id
GROUP BY addr
（2）查询公司从96年8月开始单价超过2000块的采购单号和供应商
这里我们使用了两个表一个是supply，一个是pur_item，将两个表通过供货商的id一致进行连接。
SELECT pur_no AS 采购单号,sup_name AS 供应商 FROM supply,pur_item
WHERE supply.sup_id=pur_item.sup_id and unit_price > 2000 AND pur_date >=
'1996-08-01'
（3）查询公司目前库存商品的名称和数量
我们使用stock和product两个表，通过产品编号连接，这里因为有一些产品是出自于其他的供货商，但我按名字分组，将不同供货商的同一产品归为一类。
SELECT prod_name AS 名称,sum(stk_qty) AS 数量 FROM stock,product
WHERE stock.prod_id=product.prod_id GROUP BY prod_name
（4）查询公司采购金额超过10万的定单信息（包括定单号厂家商品名）
SELECT sup_name AS 订单号厂家, prod_name AS 商品名 FROM product,supply,pur_item
WHERE pur_item.prod_id=product.prod_id AND supply.sup_id=pur_item.sup_id AND qty * unit_price > 100000
（5）查询库存量前三名的产品名称
SELECT prod_name AS 产品名称 FROM (SELECT prod_id,sum(stk_qty) as stk_qty
FROM stock GROUP BY prod_id) AS s,product
WHERE s.prod_id=product.prod_id
ORDER BY s.stk_qty DESC
LIMIT 3
（6）查询库存商品销售金额在第三到第六的供货商的信息
SELECT supply.* FROM supply,(SELECT  sup_id,sum(qty * unit_price) as price from sale_item GROUP BY sup_id) AS s
WHERE supply.sup_id=s.sup_id
LIMIT 4 OFFSET 2
（7）查询同一类型产品有两家以上供货商的产品编号以及供货商的数量
SELECT prod_id AS 产品编号,count(stock.sup_id) AS 供货商数量 FROM supply,stock
WHERE supply.sup_id=stock.sup_id
GROUP BY prod_id
HAVING 供货商数量 > 2
（8）统计公司各种产品的销售金额（需要区分不同的厂家）
SELECT prod_name AS 产品名称,qty * unit_price AS 销售金额,sup_name AS 厂家 FROM supply,sale_item,product
WHERE supply.sup_id=sale_item.sup_id AND sale_item.prod_id=product.prod_id
（9）查询公司在96年10月的定单，计算每日定单金额，并按照定单金额排序
SELECT sales.*,sum(qty * unit_price) AS 每日订单金额 FROM sales,sale_item
WHERE sales.order_no=sale_item.order_no AND YEAR(sale_item.order_date)=1996 AND MONTH(sale_item.order_date)=10
GROUP BY sales.order_no
ORDER BY sales.toa_amt
（10）查询一笔销售记录中包含有两条明细记录的销售总帐记录
SELECT sales.* FROM sales
WHERE sales.order_no IN
( 
SELECT sale_item.order_no FROM sale_item
GROUP BY sale_item.order_no
HAVING count(prod_id)=2
);
（11）查询销售总表和销售明细表中不符合参照关系的数据（定单编号为参照字段）。
SELECT sales.* FROM sales
LEFT JOIN (select sale_item.order_no FROM sale_item GROUP BY sale_item.order_no) as s  ON sales.order_no = s.order_no
WHERE s.order_no is NULL
（12）查询每个员工的工资以及应该交纳的个人所得税金额（40000以下不交， 40000---49999 5% 50000—59999 7% 60000以上 10%）
SELECT employee.emp_name AS 姓名,employee.salary AS 工资, 
CASE 
	WHEN employee.salary < 40000 THEN  0
	WHEN employee.salary BETWEEN 40000 AND 49999 THEN (employee.salary-40000)*0.05
	WHEN employee.salary BETWEEN 50000 AND 59999 then (employee.salary-50000)*0.07+9999*0.05
	ELSE (employee.salary-60000)*0.1+9999*0.05+9999*0.07
END AS 个人所得税
FROM employee
（13）生成公司销售的明细表要求表中需要表现的信息为（定单号，销售员姓名，销售产品，供伙商名称，销售金额）
SELECT sale_item.order_no AS 定单号,employee.emp_name AS 销售员姓名,product.prod_name AS  销售产品, 
supply.sup_name AS 供货商名称,qty * unit_price AS 销售金额
FROM employee,product,supply,sales,sale_item
WHERE sale_item.prod_id=product.prod_id AND supply.sup_id=sale_item.sup_id AND sales.sale_id = employee.emp_no 
AND sales.order_no=sale_item.order_no 
（14）在采购明细表中查询同类产品在不同时间进货差价超过200元的产品及供货商名称。
SELECT product.* ,supply.sup_name AS 供货商 FROM product,pur_item,supply
WHERE product.prod_id=pur_item.prod_id AND supply.sup_id=pur_item.sup_id
AND  EXISTS
	(SELECT 1 FROM pur_item AS a,pur_item AS b 
		WHERE a.prod_id =b.prod_id AND a.pur_no != b.pur_no AND a.pur_date != b.pur_date AND ABS(a.unit_price - b.unit_price) > 200 
		 and a.prod_id = pur_item.prod_id
	);
（15）查询在同一天进入公司的员工信息
SELECT a.* FROM employee AS a,employee AS b
 WHERE a.date_hired=b.date_hired and a.emp_no != b.emp_no
 ORDER BY a.date_hired
（16）查询公司所有客户在公司的定货情况
SELECT customer.cus_name AS 客户,sales.* FROM customer
LEFT JOIN sales ON customer.cus_id = sales.cus_id
（17）查询由公司女业务员所接回的定单
SELECT sales.* FROM sales,employee
WHERE employee.emp_no=sales.sale_id AND employee.sex = 'F' AND employee.dept = '业务'
（18）查询公司中姓名相同的员工并按照员工编号显示员工信息
SELECT a.* FROM employee AS a,employee AS b
 WHERE left(a.emp_name,1)=left(b.emp_name,1) and a.emp_no != b.emp_no
 GROUP BY a.emp_no ,a.emp_name
 ORDER BY a.emp_no
（19）查询公司中目前业绩还没有超过2万的业务员
SELECT e.* FROM (SELECT * FROM employee WHERE employee.title='职员' and employee.dept='业务') as e
left JOIN (SELECT sales.sale_id,sum(sales.toa_amt) as total FROM sales GROUP BY sales.sale_id ) AS s ON e.emp_no=s.sale_id 
WHERE COALESCE(total ,0)  < 20000
（20）查询仓库中还没有销售过的产品信息
SELECT product.* FROM product
JOIN stock ON product.prod_id=stock.prod_id
WHERE  not  EXISTS 
(
SELECT 1 FROM sale_item
WHERE sale_item.prod_id = stock.prod_id AND sale_item.sup_id = stock.sup_id
)GROUP BY prod_id;
（21）查询没有在公司订购产品的客户名单
SELECT customer.* FROM customer
left JOIN sales on customer.cus_id = sales.cus_id 
WHERE sales.cus_id IS NULL
（22）按照供货商来统计公司的销售榜
SELECT supply.sup_name AS 供货商,sum(qty * unit_price) AS 销售额 FROM supply,sale_item
WHERE supply.sup_id = sale_item.sup_id
GROUP BY supply.sup_name
ORDER BY 销售额 DESC