MySQL中UNION查询别名不可见问题：实现精确匹配优先的自动补全查询并避免重复结果

你的问题核心在于UNION的两个子查询是独立执行的，所以第二个子查询里无法直接引用第一个子查询的p1.id——这就是你遇到1054 Unknown column 'p1.id'错误的原因。

其实完全可以通过单次查询实现需求，而且比拆分UNION的方式更高效，同时天然避免重复。下面给你两种可行方案：

方案一：单查询通过CASE计算优先级（推荐） #

直接在一次查询里用CASE语句判断是否为精确匹配，以此设置优先级，然后排序即可。这种方式只需要扫描一次表，性能更优：

SELECT DISTINCT 
    p.*,
    -- 精确匹配（独立的"one"）优先级设为1，其他含"one"子串的设为2
    CASE WHEN CONCAT(' ', p.name, ' ') LIKE '% one %' THEN 1 ELSE 2 END AS priority,
    CASE WHEN p.parent_id IS NULL THEN 1 ELSE 2 END AS is_parent
FROM provider p
WHERE p.name LIKE '%one%' -- 先过滤所有含"one"的记录
ORDER BY priority, is_parent, id -- 按优先级、是否为父级排序
LIMIT 100;

用你的示例数据测试，这个查询会直接输出你期望的结果：精确匹配的记录排在最前面，且没有重复。

方案二：UNION结合NOT EXISTS去重（兼容原有拆分思路） #

如果你坚持要保留拆分两部分的思路，可以用NOT EXISTS子查询在第二部分排除已经被第一部分选中的记录：

SELECT * FROM (
    -- 第一部分：精确匹配独立"one"的记录
    (SELECT DISTINCT 
        p1.*, 
        1 AS priority, 
        CASE WHEN parent_id IS NULL THEN 1 ELSE 2 END AS is_parent
     FROM provider p1
     WHERE CONCAT(' ', name, ' ') LIKE '% one %' 
     LIMIT 100)
    UNION
    -- 第二部分：含"one"子串但不在精确匹配结果里的记录
    (SELECT DISTINCT 
        p2.*, 
        2 AS priority, 
        CASE WHEN parent_id IS NULL THEN 1 ELSE 2 END AS is_parent
     FROM provider p2
     WHERE name LIKE '%one%' 
       AND NOT EXISTS (
           SELECT 1 FROM provider p1 
           WHERE CONCAT(' ', p1.name, ' ') LIKE '% one %' 
             AND p1.id = p2.id
       )
     LIMIT 100)
ORDER BY priority, is_parent
) AS t LIMIT 100;

这里NOT EXISTS会检查当前p2的ID是否存在于精确匹配的结果集中，从而避免重复。

为什么你的原查询报错？ #

UNION的两个子查询是独立执行的，第一个子查询的p1别名只在它自己的作用域内有效，第二个子查询无法访问p1的任何列，所以p2.id != p1.id会触发未知列的错误。

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