MySQL查询语句综合练习

1、查询销售量最高产品的前2名(使用in关键字)

USE homework3;

①查询到前两个产品的销量和产品名
SELECT p_no,SUM(volume) mv
FROM deal
GROUP BY p_no
ORDER BY mv DESC
LIMIT 2

②通过产品编号获得前两名产品信息
SELECT *
FROM production p
WHERE p_no IN (
SELECT p_no
FROM (
SELECT p_no,SUM(volume) mv
FROM deal
GROUP BY p_no
ORDER BY mv DESC
LIMIT 2
) a
);

注：p_no派生出来的表要取别名，不然会报错；

2、查询每种产品销售量最高的销售人员(使用group by关键字)

①查询出每个产品的最高销量
SELECT MAX(volume)
FROM deal
GROUP BY p_no

②通过最高销量去匹配销售记录里的销售人员编号和销售额
SELECT s_no,volume
FROM deal
WHERE volume IN (
SELECT MAX(volume)
FROM deal
GROUP BY p_no
)

③将销售人员编号和销售额临时表连接到销售人员信息表，再查询到销售人员信息和其销售额
SELECT s.*,l.volume
FROM sales s
INNER JOIN (
SELECT s_no,volume
FROM deal
WHERE volume IN (
SELECT MAX(volume)
FROM deal
GROUP BY p_no
)
) l
ON s.s_no=l.s_no;

3、查询产品编号为2且销量量超过100的销售人员的姓名及所在公司

①查询产品编号为 2且销量超过100的销售人员编号
SELECT s_no
FROM deal
WHERE p_no=2 AND volume>100

②通过销售人员编号查询其姓名及公司
SELECT s_name,agent_name
FROM sales s
INNER JOIN agent a
ON a.agent_no=s.agent_no
WHERE s_no IN (
SELECT s_no
FROM deal
WHERE p_no=2 AND volume>100
) ;

4、查询所有代理商所有产品的销售量

①需要的是代理商名称和销售量也就是agent表和deal表，但两个表之间并没有相关联的键，
则需要一个中间表作为桥梁那就是seals表，再将得到的三表集合以代理商分组查询其销售量和
SELECT agent_name,SUM(volume)
FROM agent a,deal d,sales s
WHERE a.agent_no=s.agent_no AND s.s_no=d.s_no
GROUP BY agent_name;

5、查询每个产品有多少个销售人员在销售

①将涉及到的两个表连接起来，再以p_on分组计算其p_on的个数 因为p_on重复的就是销售人员的个数
SELECT p_no,COUNT(*)
FROM sales s
INNER JOIN deal d
ON s.s_no=d.s_no
GROUP BY p_no;

6、查询名称包含BBB的代理商中的所有销售人员

①需要使用到两个表连接起来，查找其满足条件的销售人员名字和其公司
SELECT s_name,agent_name
FROM agent a
INNER JOIN sales s
ON a.agent_no=s.agent_no
WHERE agent_name LIKE “%BBB%”;

7、查询总销量最差的产品

①查询出每个产品的总销量
②得到其产品名需要连接production表
SELECT p.p_no,p.p_name,SUM(volume)
FROM deal d
INNER JOIN production p
ON p.p_no=d.p_no
GROUP BY p_no
ORDER BY SUM(volume) ASC
LIMIT 1;

8、查询2017年生产的产品的总销量

①将涉及到的产品日期和销量两个表连接起来
②再筛选出2017年的产品再查询其整个销售额
SELECT SUM(volume)
FROM deal d,production p
WHERE d.p_no=p.p_no AND p_date BETWEEN ‘2017-1-1’ AND ‘2017-12-31’;