sql server 多重嵌套 存储过程 sql嵌套多表查询

Key word#多表查询（子查询+联结+组合查询）

1.标量子查询
    子查询：嵌套在其他查询中的查询
    标量子查询：只返回一行一列（即一个单元格数据）的子查询，相当于一个常数；也可以当作常数列加在结尾。
    注意：子查询可多层嵌套，不限制嵌套层数，但性能会有影响

2.关联子查询
    嵌套再其他查询中的查询，适用于组内比较。
    子查询返回一列数据：子查询的结果，与主查询的目标列存在一定关联

3.普通子查询
    嵌套再其他查询中的查询。
    子查询返回一列数据：将子查询的结果列，作为主查询的取值范围。（#把in/大于小于这类的范围用select语句代替）
    PS:将二维表作为主查询新的检索表


★★★表联结
    检索数据时必须用联结
    联结：一种机制，关联多个表，返回一组输出，在一条select语句中实现；主键：唯一标识，可以是ID或者其他唯一值（身份证号）
    补充：联接不是物理实体，在实际数据库表中不存在
    联接方式 “=”，有些类似于excel中的 VLOOKUP
    分类：
        1. ★内部联结（INNER JOIN)
            前提-必须有共同列,等值联结
            结果-所有表的所有列、共同列重复的行
            创建联结规则-列出所有表、定义所有表关系
                例如：所有表-pro_info AS p, supplier_info AS s, order_list AS l，那么表关系有
                p.supplier_id=s.supplier_id；p.prod_id=l.prod_id；三张表里两两联结了
                pro_info AS p, supplier_info AS s, order_list AS l中，三个表中的“，”可以用inner join代替，后面如果有where 用on 代替

            相比子查询，性能更好，可以从多个表中选择结果字段

        2. 自联结
            前提-联结表为同一个
            结果-返回所有数据
            创建联结规则-列出所有表、定义所有表关系
                例如：所有表-pro_info AS p1，pro_info AS p2
                    p1.supplier_id = p2.supplier_id；p1.prod_name='原味薯片'；p1.brand='乐事'；

                注意输出结果字段的选择（注意别名）


        3. ★★外部联接（LEFT OUTER JOIN/ RIGHT OUTER JOIN/ FULL OUTER JOIN）
            前提-必须有共同列
            结果-所有表的所有列、包含相关表中没有关联行的行
            创建联结规则-列出所有表、定义所有表关系
                左外部联结-包含左边表的所有行：LEFT OUTER JOIN
                右外部联结-包含右边表的所有行：RIGHT OUTER JOIN
                全外部联结-包含两边表的所有行：FULL OUTER JOIN

            注意联结语法、联结条件、多表联结时，先分别测试每个联结

        在联结中使用聚合函数
            联结>>嵌套>>聚合>>简化
            先联结得到一个虚拟表；对虚拟表进行二次查询；在虚拟表基础上进行聚合操作；剥除虚拟表，留下作用语句
            示例：SELECT cust_id, cust_name, COUNT(DISTINCT order_id) order_num  FROM( ) a  GROUP BY cust_id;


    组合查询—UNION
        多条select语句：UNION + SELECT + 列字段 + FROM +单个/多个表 + 'order by'
        要求：自动过滤重复行+两条及两条以上的select语句；每两条之间union一次；union all关键字
        列字段要求：相同的列、表达式或者聚集函数；数据类型必须兼容（允许隐式转换）；最终字段名由第一条SELECT语句决定
        ORDER BY：最后一条SELECT 语句中，作用整个检索结果

关于代码：

-- 多表查询
SELECT * from milk_tea as m;

SELECT m.sale_price FROM milk_tea as m WHERE m.prod_name = '方便面';

SELECT * 
From milk_tea as m1 
WHERE m1.sale_price > (
	                  SELECT m.sale_price 
					  FROM milk_tea as m 
			          WHERE m.prod_name = '方便面');

SELECT m1.* ,
					(SELECT m.sale_price 
					FROM milk_tea AS m 
					WHERE m.prod_name = '奶茶')
FROM milk_tea AS m1;

SELECT * 
FROM prod_info as p;

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 = '奶茶');

-- 3个标量子查询并在一起
SELECT *
FROM prod_info as p2
where  p2.sale_price >(SELECT avg(p.sale_price) 
					FROM prod_info as p
					WHERE p.class = p2.class
					group by p.class);

-- 继续子查询
SELECT m.prod_name FROM milk_tea AS m WHERE m.sale_price = 15;
SELECT * FROM milk_tea as m where m.prod_name in ('奶茶','薯片','薯条');

-- 注意区分好m1与m2
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);

SELECTlect * from prod_info;

SELECT p.prod_name,p.type,p.sale_price FROM prod_info as p WHERE prod_name = '抽纸';
SELECT a.type FROM (SELECT p.prod_name,p.type,p.sale_price FROM prod_info as p WHERE prod_name = '抽纸') as a WHERE a.sale_price > 26; 

-- 联接
SELECT * FROM prod_info;
SELECT * FROM supplier_info;

SELECT p.*,s.* 
FROM prod_info AS p,supplier_info as S
WHERE p.supplier_id = s.supplier_id;

SELECT * FROM order_list;
SELECT * from prod_info;

SELECT l.prod_id FROM order_list AS l WHERE l.order_id = '20190403001';

SELECT * FROM prod_info AS P WHERE p.prod_id IN (SELECT l.prod_id FROM order_list AS l WHERE l.order_id = '20190403001');

-- 内部联结
SELECT p.*, l.*
FROM prod_info AS p, order_list AS l
WHERE p.prod_id = l.prod_id
 AND l.order_id = '20190403001';

-- 改联结查询
SELECT p.*, l.*
FROM prod_info AS p INNER JOIN order_list AS l
ON p.prod_id = l.prod_id
 AND l.order_id = '20190403001';

SELECT * FROM cust_info AS c;
SELECT * FROM order_list AS l;
-- 假设要查19年4月7日购买情况
SELECT * FROM order_list AS l WHERE l.order_id LIKE '20190407%';
-- 假设要查当天购买+客户信息
SELECT c.*,l.* 
FROM cust_info AS c LEFT OUTER JOIN order_list AS l 
ON c.cust_id = l.cust_id
AND l.order_id LIKE '20190401%';
-- 结果保留了所有客户信息，但是0401没有购买商品的客户，数据表没有保存

-- 查看每个客户购买了多少商品
SELECT c2.cust_id, COUNT(c2.prod_id)
FROM(SELECT c.cust_id, c.cust_name, l.prod_id, l.prodname, l.order_id
		FROM cust_info AS c LEFT OUTER JOIN order_list AS l 
		ON c.cust_id = l.cust_id
		AND l.order_id LIKE '20190401%') AS c2
GROUP BY c2.cust_id;

-- 直接对联结进行聚合，把FROM后面的部分当成结果表，整体再进行分组聚合

SELECT c.cust_id, COUNT(l.prod_id)
FROM cust_info AS c LEFT OUTER JOIN order_list AS l 
ON c.cust_id = l.cust_id
AND l.order_id LIKE '20190401%'
GROUP BY c.cust_id;

-- UNION写法
SELECT * FROM order_list AS l WHERE l.order_id LIKE '20190407%'
UNION ALL（区分UNION 和 UNION ALL）
SELECT * FROM order_list AS l WHERE l.order_id LIKE '20190407%';

– 习题补充
/* 多表查询例题，用产品信息表与流水表查询
1、找出所有洁云牌抽纸的销售流水（使用标量子查询）。
2、找出各类商品中，价格高于该类商品均价的部分（使用关联子查询）。
3、用联结的方法改写第一题。
4、归总下所有商品的销量。
*/

SELECT * FROM prod_info;
SELECT * FROM order_list;

– 标量子查询 找出所有洁云牌抽纸的销售流水

SELECT * 
FROM order_list as l 
WHERE l.prod_id = (
						SELECT p.prod_id 
						FROM prod_info as p 
						WHERE p.prod_name = '抽纸' 
						and p.brand = '洁柔') ;

– 关联子查询:找出各类商品中，价格高于该类商品均价的部分（使用关联子查询）

SELECT * 
FROM prod_info as p1
WHERE p1.sale_price >(
							SELECT AVG(sale_price) 
							FROM prod_info as p2 
							WHERE p2.class = p1.class 
							GROUP BY p1.class);

– 用联结的方式改写第一题

SELECT * 
FROM order_list as l INNER JOIN prod_info as p
ON p.prod_name = '抽纸' and p.brand = '洁柔' 
and p.prod_id = l.prod_id;

– 归总下所有商品的销量

SELECT p.prod_name, p.brand, SUM(l.quantity) as Quantity 
FROM prod_info as p LEFT OUTER JOIN order_list as l
ON p.prod_id = l.prod_id 
GROUP BY p.prod_name, p.brand;