SQL集合运算

ops/2024/11/17 19:06:17/

集合论是SQL语言的根基。

1 集合运算

注意事项：

1）SQL能操作具有重复行的集合，可以通过可选项ALL来支持。

如果直接使用UNION或INTERSECT，结果里不会出现重复的行。如果想在结果里留下重复行，可以加上可选项ALL。写作UNION ALL。

集合运算符为了排除掉重复行，会默认发生排序，而加上可选项ALL之后，就不会再排序了，所以性能会提升。

2) 集合运算符有优先级。

INTERSECT比UNION和EXCEPT的优先级更高。

1.1 实践

1.1.1 检查集合相等性

图两个集合t_table_a与t_table_b

-- UNION,如果合并后与两个集合的行数一致，则两个集合相同
SELECT CASE 
WHEN COUNT(*) = (SELECT COUNT(*) FROM t_table_a)
AND COUNT(*) = (SELECT COUNT(*) FROM t_table_b) 
THEN '集合相等'  ELSE '集合不相等' END AS res 
FROM 
(SELECT *
FROM t_table_a
UNION
SELECT *
FROM t_table_b)tmp
-- 集合运算，如果A与B的并集等于A与B的交集。 则A=B
SELECT CASE WHEN COUNT(*) = 0 THEN '相等' ELSE '不相等' END AS res
FROM 
((SELECT *
FROM t_table_a
UNION 
SELECT *
FROM t_table_b)
EXCEPT 
(SELECT *FROM t_table_aINTERSECT SELECT *FROM t_table_b
))tmp;

1.1.2 用差集实现关系除法运算

图员工技能t_emp_skills 表与技能t_skills 表及期望输出

需求：找出精通t_skills 表所有技能的员工。

-- 差集 EXCEPT
SELECT DISTINCT emp 
FROM t_emp_skills e 
WHERE NOT EXISTS 
(SELECT skillFROM t_skillsEXCEPT SELECT skill FROM t_emp_skills WHERE emp = e.emp 
);

需求：找出刚好拥有全部技术的员工（即擅长的技能和技能表的一摸一样，不多也不少）。

SELECT emp 
FROM t_emp_skills e 
WHERE NOT EXISTS ((SELECT skillFROM t_skills EXCEPT SELECT skill FROM t_emp_skillsWHERE emp = e.emp)
)
GROUP BY emp 
HAVING COUNT(*) = (SELECT COUNT(*) FROM t_skills);

1.1.3 寻找相等的子集

图供应商-零件关系t_sup_parts表及期望输出

需求：找出经营的零件在种类数和种类上都完全相同的供应商组合。

SELECT s1.sup sup1,s2.sup sup2
FROM t_sup_parts s1
CROSS JOIN t_sup_parts s2 
WHERE s1.sup < s2.sup AND s1.part = s2.part
GROUP BY s1.sup,s2.sup
HAVING COUNT(*) = (SELECT COUNT(*) FROM t_sup_parts WHERE sup = s1.sup)
AND COUNT(*) = (SELECT COUNT(*) FROM t_sup_parts WHERE sup = s2.sup);

1.1.4 高效删除重复行

图存在重复数据的t_fruit_info表

需求：删除表中重复的数据。

-- 使用关联子查询
DELETE FROM t_fruit_info f
WHERE row_id < (SELECT *FROM (SELECT MAX(row_id)FROM t_fruit_info WHERE `name` = f.name AND price = f.price) temp
);

关联子查询性能比较差。

-- 用差集运算 
DELETE FROM t_fruit_info
WHERE row_id IN (SELECT * FROM (SELECT row_idFROM t_fruit_info EXCEPT (SELECT row_idFROM t_fruit_infoGROUP BY `name`,price)) tmp
);
-- NOT IN 求补集 
DELETE FROM t_fruit_info
WHERE row_id NOT IN (SELECT * FROM (SELECT MAX(row_id)FROM t_fruit_info GROUP BY `name`,price) tmp
);