sql server多表查询去除多余 sql中多表查询

SQL学习笔记 | 09 多表查询

• 一、子查询

• 1、概念
• 2、种类

• （1）、标量子查询（一个常数）
• （2）、关联子查询（一列值）
• 
  
• （3）、普通子查询（一列值）

• 二、表联结（拼列）

• 1、概念
• 2、种类

• （1）、内联结（显示重复行）
• （2）、自联结
• （3）、外联结（显示重复行、无关联信息）

• 3、应用

• 三、组合查询（拼行）

• 1、概念
• 2、种类

• （1）UNION（去重）
• （2）UNION ALL（不去重）

• 3、Navicat代码

一、子查询

1、概念

2、种类

（1）、标量子查询（一个常数）

• 返回值：一个常数
• 放置位置：
  SELECT 后插入一个常数列。如：SELECT m.prod_id, 15 FROM
  WHERE 后的过滤条件中。如：WHERE m.sale_price > 15
  HAVING 后的过滤条件中。如：HAVING COUNT(m.sale_price) > 2
• Navicat代码

#	放在WHERE后（Result 1）
SELECT *
FROM milk_tea AS m1
WHERE m1.sale_price > ( 
                       SELECT m.sale_price 
					   FROM milk_tea AS m 
					   WHERE m.prod_name = '奶茶')			#	子查询结果为：15，下同


											
#	放在SELECT后（Result 2）											
SELECT m1.*, (
              SELECT m.sale_price 
			  FROM milk_tea AS m 
			  WHERE m.prod_name = '奶茶') '15'
FROM milk_tea AS m1;	



#	放在HAVING后（Result 3）
SELECT p.class, AVG(p.sale_price) 
FROM prod_info AS p
GROUP BY p.class
HAVING AVG(p.sale_price) > (
                            SELECT m.sale_price 
						    FROM milk_tea AS m 
						    WHERE m.prod_name = '奶茶');

（2）、关联子查询（一列值）

• 返回值：一列值
  注：可以看作一列值，也可以看作一个n乘1的二维表
• 用法：
  子查询SELECT的列 和 主查询中SELECT的列相同。
  子查询的WHERE后，一般是子查询的某一列=主查询的某一列。
• 作用：
  将多个相同结构的标量子查询组合在一起。
  如；标量子查询只能查找大于日用品均值的日用品。而关联子查询能查出大于其均值的所有类别，日用品，饮料，零食。
• Navicat代码

#	标量子查询，实现某个类别（日用品）的对比。Result 1
SELECT *
FROM prod_info AS p2
WHERE p2.class = '日用品'
  AND p2.sale_price > ( 
                       SELECT AVG(p1.sale_price)
					   FROM prod_info AS p1
					   WHERE p1.class = '日用品'
					   GROUP BY p1.class);


#	关联子查询，实现所有类别的对比。Result 2										 
SELECT *
FROM prod_info AS p2
WHERE  p2.sale_price > ( 
                        SELECT AVG(p1.sale_price)
						FROM prod_info AS p1
					    WHERE p1.class = p2.class
						GROUP BY p1.class);

（3）、普通子查询（一列值）

• 返回值：一列值
  注：可以看作一列值，也可以看作一个n乘1的二维表
• 放置位置：
  作为一个表，作为主查询的查询表
  作为一列值，作为主查询WHERE IN （括号中的可选值）
• Navicat代码

#	作为IN后面不连续的取值（Result 1）
SELECT *
FROM milk_tea AS m1
WHERE m1.prod_name IN (
                       SELECT m2.prod_name
					   FROM milk_tea AS m2
					   WHERE m2.sale_price = 15);



#	作为主查询的查询表（Result 2）											 
SELECT b.type 
FROM (
      SELECT p.prod_name, p.type, p.sale_price 
	  FROM prod_info AS p 
	  WHERE p.prod_name = '抽纸')	AS b 
WHERE	b.sale_price >26;

二、表联结（拼列）

1、概念

• 联结前提：两张表必须有相同的列。
• 联结不改变实际数据库中的表。

#	这其实是内联结的写法
SELECT * 
FROM prod_info AS p, supplier_info AS s 
WHERE p.supplier_id = s.supplier_id;

prod_info表（备用对比）：

supplier_info表（备用对比）：

内联结：

2、种类

（1）、内联结（显示重复行）

• 表之间用：逗号改为INNER JOIN 或 “=”
• 过滤条件“WHERE”改为“ON”
• 联结结果：显示共同列中重复的行！
• ON后面除了联结字段相等，还可以再用AND加过滤条件

#	写法1（表联结概念中展示的）
SELECT p.*,o.*
FROM prod_info AS p, order_list AS o
WHERE p.prod_id = o.prod_id
AND o.order_id = '20190403001';					#	两种写法结果完全一样

#	写法2（推荐用，意义更明确）
SELECT p.*,o.*
FROM prod_info AS p INNER JOIN order_list AS o
ON p.prod_id = o.prod_id
AND o.order_id = '20190403001';

（2）、自联结

• 特殊的内联结
• 两张联结表相同

（3）、外联结（显示重复行、无关联信息）

• 联结结果：不仅包含相同列中的重复行（内联结的功能），还包含相关表中没有关联的行。例如：客户信息表（500个客户）和超市某天的流水帐表（100条记录，同一个人可能有两条记录）。外联结的结果，不仅同一个人的两条记录会在不同行显示，而且其余在今天没有买东西的客户信息也会显示
• 要想清，显示哪张表的所有信息
• 外联结的种类：

#	要显示所有客户在4月7号的账目，有的人没买东西计成NULL
SELECT c.*, o.*
FROM cust_info AS c LEFT OUTER JOIN order_list AS o
ON c.cust_id = o.cust_id
AND o.order_id LIKE '20190407%';

结果如下：

cust_info表（备用对比）：

order_list表（备用对比）：

外联结：

3、应用

将联结后的新表作为一个查询表，对其进行分组聚合。

#	对外联结结果进行分组聚合，FROM里面的是外联结的表（推荐写法）
SELECT  c2.cust_id, COUNT(c2.prod_id)
FROM (SELECT c.cust_id, c.cust_name, prod_id,o.prodname, o.order_id 
      FROM cust_info AS c LEFT OUTER JOIN order_list AS o
      ON c.cust_id = o.cust_id
			AND o.order_id LIKE '20190401%') c2
GROUP BY c2.cust_id;											#两种写法结果相同



#	也可以换一种写法，这种写法不如上面的写法明确（不推荐）
SELECT c.cust_id, COUNT(o.prod_id) 
FROM cust_info AS c LEFT OUTER JOIN order_list AS o
ON c.cust_id = o.cust_id
AND o.order_id LIKE '20190401%'
GROUP BY c.cust_id;

三、组合查询（拼行）

1、概念

• 组合查询是将查询结果（多条完整的SELECT语句）进行连接，相比表联结来说比较简单
• 每两条SELECT语句就要UNION一次
• SELECT的列字段必须都相同（可以是列、表达式、聚合函数，但内容必须相同），且数据类型要相同（允许隐式转换，如文本型‘123’与数字型123兼容）。但FROM的表不必相同。
• 最终表的列名由第一条SELECT语句的列字段决定。
• ORDER BY是针对最终拼接结果的。

2、种类

（1）UNION（去重）

（2）UNION ALL（不去重）

3、Navicat代码

这里是对同一张表UNION的，其实实际意义不大。如果有order_list 1和order_list 2，不同时间的两张订单表，这时候进行相同字段的拼接比较好。

#	UNION，去重（Result 1）
SELECT * FROM order_list AS o WHERE o.order_id LIKE '20190407%'
UNION  
SELECT * FROM order_list AS o WHERE o.order_id LIKE '20190407%';



#UNION ALL，不去重，用的相对较少（Result 2）
SELECT * FROM order_list AS o WHERE o.order_id LIKE '20190407%'
UNION ALL 
SELECT * FROM order_list AS o WHERE o.order_id LIKE '20190407%';