mysql里两条SQL取交集如何取

集合运算

集合在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合。

在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

表加法–union

SELECT product_id, product_name
  FROM product
 UNION
SELECT product_id, product_name
  FROM product2;

• 对同一个表，union和or都能起到相同效果。
• 包含重复行，union all

MySQL 8.0及以前 不支持交运算INTERSECT

此时需要用 inner join 来求得交集

SELECT p1.product_id, p1.product_name
  FROM Product p1
INNER JOIN Product2 p2
ON p1.product_id=p2.product_id

MySQL 8.0 还不支持 EXCEPT 运算

用not in 谓词实现

找出只存在于Product表但不存在于Product2表的商品。

-- 使用 NOT IN 子句的实现方法
SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM Product2)

实现对称差

-- 使用 NOT IN 实现两个表的差集
SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id FROM Product2)
UNION
SELECT * 
  FROM Product2
 WHERE product_id NOT IN (SELECT product_id FROM Product)

连结join

内连结inner join

-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

问题的关键是, 找出一个类似于"轴"或者"桥梁"的公共列, 将两张表用这个列连结起来。这就是连结运算所要作的事情。

• 要点一: 进行连结时需要在 FROM 子句中使用多张表.
• 要点二:必须使用 ON 子句来指定连结条件.
• 要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用。
• 如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边。

包含重复行的集合运算 UNION ALL

-- 保留重复行
SELECT product_type
  FROM Product
 UNION ALL
SELECT product_type
  FROM Product2;

练习题:

商店决定对product表中成本利润低于50% 或者 售价低于1000的商品提价, 请使用UNION ALL 语句将分别满足上述两个条件的结果取并集. 查询结果类似下表:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AZC8UP15-1690645973216)(./ch04.06result3.png)]

参考答案

SELECT * 
  FROM Product 
 WHERE sale_price < 1000
 UNION ALL
SELECT * 
  FROM Product 
 WHERE sale_price < 1.5 * purchase_price

隐式数据类型转换

通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型:

SELECT product_id, product_name, '1'
  FROM Product
 UNION
SELECT product_id, product_name,sale_price
  FROM Product2;

需要注意的是 hive中进行join关联时，关联列要避免使用隐式数据类型转换，否则容易导致数据倾斜

自然连结

当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。

外连结

左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充。

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

• 结果包含主表中所有信息，主表通过left，right指定

多表内连结

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
       ,IP.inventory_quantity
  FROM ShopProduct AS SP
 INNER JOIN Product AS P
    ON SP.product_id = P.product_id
 INNER JOIN InventoryProduct AS IP
    ON SP.product_id = IP.product_id
 WHERE IP.inventory_id = 'P001';

多表外连结

SELECT P.product_id
       ,P.product_name
       ,P.sale_price
       ,SP.shop_id
       ,SP.shop_name
       ,IP.inventory_quantity
  FROM Product AS P
  LEFT OUTER JOIN ShopProduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id

ON 子句进阶–非等值连结

使用比较运算符来进行连接. 实际上, 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件。

习题

1. 找出 product 和 product2 中售价高于 500 的商品的基本信息。

select *
from product

union

select *
from product2
where sale_price >500;

2. 借助对称差的实现方式, 求product和product2的交集。

select *
from product
where product_id not in (select product_id from product2)

union

select *
from product2
where product_id not in (select product_id from product);

3. 每类商品中售价最高的商品都在哪些商店有售 ？

select p.product_type, sp.shop_name, MAX(p.sale_price)
from product as p
inner join shopproduct as sp
on p.product_id = sp.product_id 
group by sp.shop_name, p.product_type ;

4. 分别使用内连结和关联子查询每一类商品中售价最高的商品。

-- 使用内连结查询每一类商品中售价最高的商品。
SELECT p1.product_type, p1.product_name, p1.sale_price
FROM product AS p1
INNER JOIN (
  SELECT product_type, MAX(sale_price) AS max_sale_price
  FROM product
  GROUP BY product_type
) AS p2 ON p1.product_type = p2.product_type AND p1.sale_price = p2.max_sale_price;

-- 使用关联子查询查询每一类商品中售价最高的商品。
select product_name, product_type, sale_price
FROM product AS p1
WHERE sale_price = (
    SELECT MAX(sale_price)
    FROM product AS p2
    WHERE p1.product_type = p2.product_type
);

5. 用关联子查询实现：在 product 表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。

SELECT product_id, product_name, sale_price, 
       (SELECT SUM(sale_price) 
        FROM product p2 
        WHERE p2.sale_price <= p1.sale_price) AS cumulative_sum
FROM product p1
ORDER BY sale_price;