使用union all或distinct或not的正确方法

pinkon5k 于 2021-06-19 发布在 Mysql

关注(0)|答案(2)|浏览(393)

我想我不是在用最好的方法。我的目标是从两个单独的表（即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

我还有重复的记录。
或者，我尝试了“不在”选项，但没有任何效果-当然我知道我很接近

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)

我需要一些向导。

mysql union Distinct

来源：https://stackoverflow.com/questions/52913978/correct-way-to-use-union-all-or-distinct-or-not-in

2条答案

按热度按时间

pvabu6sv1#

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

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);

赞(0）回复(0）举报 2021-06-20

jtoj6r0c2#

请尝试以下操作：

select DISTINCT(subTable.column)
form (
    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
) as subTable

赞(0）回复(0）举报 2021-06-20

我来回答

使用union all或distinct或not的正确方法

2条答案

相关问题

热门标签

最新问答