mysql - 使用 UNION ALL 或 DISTINCT 或 NOT IN 的正确方法

Question

我想我在这里没有使用最好的方法。我的目标是从两个单独的表中获取唯一记录，即 table2016 和 table2015

SELECT GROUP_CONCAT(coursecode) FROM table2016 WHERE regno = 'ABC/XY/DEF/12/155' AND (ca_score + exam_score) <= 39
UNION DISTINCT
SELECT GROUP_CONCAT(coursecode) FROM table2015 WHERE regno = 'ABC/XY/DEF/155' AND (ca_score + exam_score) <= 39

这是我得到的:

GROUP_CONCAT(coursecode)
========================
EDM305,EDU313,EDU498
EDM305,EDU313,EDU497

我还有重复的记录。

或者，我尝试了 NOT IN 选项，但无济于事 - 当然我知道这很接近

SELECT GROUP_CONCAT(coursecode) FROM table2016 WHERE regno = 'ABC/XY/DEF/12/155' AND (ca_score + exam_score) <= 39
NOT IN
(SELECT GROUP_CONCAT(coursecode) FROM table2015 WHERE regno = 'ABC/XY/DEF/155' AND (ca_score + exam_score) <= 39)

我可以使用一些指南。

Answer 1

您可以使用子查询来合并两个表中的行，然后将它们连接起来:

SELECT GROUP_CONCAT(coursecode)
FROM (
    SELECT coursecode FROM table2016
    WHERE regno = 'ABC/XY/DEF/12/155' AND (ca_score + exam_score) <= 39
    UNION 
    SELECT coursecode FROM table2015 
    WHERE regno = 'ABC/XY/DEF/155' AND (ca_score + exam_score) <= 39
   ) s
WHERE coursecode NOT IN (SELECT coursecode 
                         FROM table2017
                         WHERE regno = 'ABC/XY/DEF/155' 
                           AND (ca_score + exam_score) >= 40);