SQL进阶教程1-7 集合运算练习题

 

　　

　　如果相等则会输出一行数据 ‘相等’，否则会显示空行。

SELECT '相等' AS result
FROM (SELECT * FROM tbl_a
            UNION
            SELECT * FROM tbl_b) temp
HAVING COUNT(*) = (SELECT COUNT(*) FROM tbl_a)
        AND COUNT(*) = (SELECT COUNT(*) FROM tbl_b)

 

　　

原SQL:

SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE NOT EXISTS
            (SELECT skill
                FROM Skills
                EXCEPT
                SELECT skill
                FROM EmpSkills ES2
                WHERE ES1.emp = ES2.emp);

答案：

SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE NOT EXISTS
            (
                (
                SELECT skill
                FROM Skills
                EXCEPT
                SELECT skill
                FROM EmpSkills ES2
                WHERE ES1.emp = ES2.emp
                )
                UNION
                (
                SELECT skill
                FROM EmpSkills ES2
                WHERE ES1.emp = ES2.emp
                EXCEPT
                SELECT skill
                FROM Skills
                )

);