如何合并三个SQL查询并按优先级排序且去除重复结果？

要实现按查询优先级排序且无重复记录的需求，核心思路是给每条符合条件的记录标记最高优先级，再对重复记录只保留优先级最高的条目，最后排序。以下是两种可行的实现方式：

方法一：使用CASE+GROUP BY去重并排序 #

SELECT o.*
FROM (
    SELECT 
        *,
        -- 按匹配严格程度分配优先级：0最高，2最低
        CASE
            WHEN full_name LIKE 'Mohammed Ali%' THEN 0
            WHEN full_name LIKE '%Mohammed Ali%' THEN 1
            WHEN full_name LIKE '%Mohammed%Ali%' THEN 2
        END AS ord
    FROM orders
    WHERE full_name LIKE '%Mohammed%Ali%' -- 覆盖所有三个查询的结果范围
) o
-- 按记录唯一标识分组（假设id是主键，需替换为你的实际唯一字段）
GROUP BY o.id
-- 只保留每个记录中优先级最高的条目（最小ord值）
HAVING MIN(o.ord) = o.ord
-- 按优先级、日期倒序排序
ORDER BY o.ord, o.g_date DESC;

逻辑说明： #

1. 优先级标记：通过CASE语句给每条符合条件的记录分配优先级，匹配规则越严格，优先级数值越小（优先级越高）。
2. 范围覆盖：WHERE条件用最宽松的%Mohammed%Ali%，确保包含三个原始查询的所有结果。
3. 去重处理：按记录的唯一主键分组，通过HAVING MIN(o.ord) = o.ord保留每个记录的最高优先级条目。
4. 最终排序：先按优先级排序，同优先级内按g_date倒序排列。

方法二：使用窗口函数ROW_NUMBER()实现 #

如果你的SQL支持窗口函数（如MySQL 8.0+、PostgreSQL、SQL Server等），这种方式更灵活：

SELECT *
FROM (
    SELECT 
        *,
        -- 给每个唯一记录的条目按优先级排序，rn=1为最高优先级
        ROW_NUMBER() OVER (
            PARTITION BY id -- 按唯一主键分组
            ORDER BY 
                CASE
                    WHEN full_name LIKE 'Mohammed Ali%' THEN 0
                    WHEN full_name LIKE '%Mohammed Ali%' THEN 1
                    ELSE 2
                END,
                g_date DESC
        ) AS rn
    FROM orders
    WHERE full_name LIKE '%Mohammed%Ali%'
) o
WHERE rn = 1 -- 只保留每个记录的最高优先级条目
-- 按要求排序
ORDER BY 
    CASE
        WHEN full_name LIKE 'Mohammed Ali%' THEN 0
        WHEN full_name LIKE '%Mohammed Ali%' THEN 1
        ELSE 2
    END,
    g_date DESC;

逻辑说明： #

1. 窗口函数分组排序：PARTITION BY id将同一条记录的不同优先级条目分组，ORDER BY确保组内按优先级、日期排序，ROW_NUMBER()给每个组内的条目编号。
2. 去重筛选：WHERE rn = 1只保留每个组中优先级最高的第一条记录。
3. 最终排序：按优先级和日期倒序输出结果。

为什么之前的尝试有问题？ #

• 第一种UNION方法：UNION会自动去重，但UNION不保证结果顺序（除非最后加ORDER BY），且无法区分不同查询的优先级，导致结果混合。
• 第二种加ord字段的UNION方法：因为同一条记录在不同查询中会被标记不同的ord，UNION会把它们视为不同的记录（字段值不同），因此无法去重，出现重复条目。

内容的提问来源于stack exchange，提问作者AKMalkadi