mysql union查询返回重复的值，即使使用groupby

9rygscc1 于 2021-06-23 发布在 Mysql

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

我有以下疑问：

SELECT *
FROM (
    SELECT 
        m.id AS id,
        reference_id, 
        title, 
        created_by, 
        publish_up, 
        state 
    FROM z_news_master m  
    LEFT JOIN z_news_english c ON m.id = c.reference_id
    WHERE c.created_by = 17152
    ORDER by c.id DESC
) AS A

UNION

SELECT * FROM (
    SELECT 
        m.id AS id, 
        reference_id,
        title, 
        created_by, 
        publish_up, 
        state 
    FROM z_news_master m  
    LEFT JOIN z_news_spanish c ON m.id = c.reference_id
    WHERE c.created_by = 17152
    ORDER by c.id DESC 
) AS B
GROUP BY id

基本上，我有三张table( z_news_master, z_news_english, z_news_spanish )，以西班牙语或英语存储新闻。这个 z_news_master 表包含通用新闻信息 z_news_english 以及 z_news_spanish 以各自的语言包含新闻。
我需要得到一个新闻列表，如果新闻是在两个语言表它应该只返回一个（不重复），上面的代码做的工作，但如果有一个新的英语和西班牙语，记录得到重复。
我也想知道为什么 GROUP BY id 以及 GROUP BY reference_id 不工作？

mysql

来源：https://stackoverflow.com/questions/50570151/mysql-union-query-return-duplicate-values-even-using-group-by

2条答案

按热度按时间

8zzbczxx1#

你可以不用工会来做这件事。下面的语句获取主表中的所有行，并连接西班牙语和英语表中的所有现有行。如果西班牙语表中存在行，它将使用该表中的值。如果英语表中存在行，而西班牙语表中不存在行，则使用该表中的值。如果英语表或西班牙语表中都不存在匹配行，则返回主表中的列。
你可以通过改变时间的顺序来改变优先级。

SELECT 
    CASE 
        WHEN NOT s.id IS NULL THEN s.id 
        WHEN NOT e.id IS NULL THEN e.id 
        ELSE m.id AS `id`, 
    CASE 
        WHEN NOT s.reference_id IS NULL THEN s.reference_id 
        WHEN NOT e.reference_id IS NULL THEN e.reference_id 
        ELSE m.reference_id AS `reference_id`, 
    CASE 
        WHEN NOT s.title IS NULL THEN s.title 
        WHEN NOT e.title IS NULL THEN e.title 
        ELSE m.title AS `title`, 
    CASE 
        WHEN NOT s.created_by IS NULL THEN s.created_by 
        WHEN NOT e.created_by IS NULL THEN e.created_by 
        ELSE m.created_by AS `created_by`, 
    CASE 
        WHEN NOT s.publish_up IS NULL THEN s.publish_up 
        WHEN NOT e.publish_up IS NULL THEN e.publish_up 
        ELSE m.publish_up AS `publish_up`, 
    CASE 
        WHEN NOT s.state IS NULL THEN s.state 
        WHEN NOT e.state IS NULL THEN e.state 
        ELSE m.state AS `state`
FROM z_news_master m  
LEFT JOIN z_news_spanish s ON m.id = s.reference_id
LEFT JOIN z_news_english e ON m.id = e.reference_id
WHERE m.created_by = 17152
ORDER by m.id DESC 
GROUP BY m.id

编辑
根据保罗·斯皮格尔的评论，这里有一个更简短的版本：

SELECT 
    COALESCE(s.id, e.id, m.id) AS `id`, 
    COALESCE(s.reference_id, e.reference_id, m.reference_id) AS `reference_id`, 
    COALESCE(s.title, e.title, m.title) AS `title`, 
    COALESCE(s.created_by, e.created_by, m.created_by) AS `created_by`, 
    COALESCE(s.publish_up, e.publish_up, m.publish_up) AS `publish_up`, 
    COALESCE(s.state, e.state, m.state) AS `state`
FROM z_news_master m  
LEFT JOIN z_news_spanish s ON m.id = s.reference_id
LEFT JOIN z_news_english e ON m.id = e.reference_id
WHERE m.created_by = 17152
ORDER by m.id DESC 
GROUP BY m.id

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

os8fio9y2#

如果存在首选语言的对应行，则使用not exists子查询删除回退语言行。假设首选语言为“英语”，查询将为：

SELECT 
    m.id AS id,
    reference_id, 
    title, 
    created_by, 
    publish_up, 
    state 
FROM z_news_master m  
JOIN z_news_english c ON m.id = c.reference_id
WHERE c.created_by = 17152

UNION ALL

SELECT 
    m.id AS id, 
    reference_id,
    title, 
    created_by, 
    publish_up, 
    state 
FROM z_news_master m  
JOIN z_news_spanish c ON m.id = c.reference_id
WHERE c.created_by = 17152
  AND NOT EXISTS (
    SELECT *
    FROM z_news_english e
    WHERE e.reference_id = m.id
      AND e.created_by = c.created_by
  )

ORDER by id DESC

请注意，不需要分组方式。左连接没有意义，因为右表中的列有一个where条件（这会将左连接转换为内部连接）。

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

我来回答

mysql union查询返回重复的值，即使使用groupby

2条答案

相关问题

热门标签

最新问答