如何在多表关联的SELECT查询中添加自定义文本的前置虚拟行？

核心思路是用UNION ALL合并自定义虚拟行的查询与原多表关联查询，再通过排序字段确保虚拟行处于结果集最前端。

具体SQL示例 #

-- 自定义虚拟行，设置排序优先级1
SELECT 'bla' AS x, 'blabla' AS y, 'blab' AS z, 1 AS sort
UNION ALL
-- 原多表关联查询，设置排序优先级2
SELECT table1.x, table2.y, table3.z, 2 AS sort
FROM table1
LEFT JOIN table2
ON ...
LEFT JOIN table3
ON ...
-- 按sort字段排序，保证虚拟行在最上方
ORDER BY sort

关键注意事项 #

• 字段类型匹配：虚拟行的每个字段类型必须与原查询对应字段一致。如果原字段是数值型（如INT、DECIMAL），需将自定义文本转换为兼容类型，例如CAST('bla' AS VARCHAR(50)) AS x（根据实际字段长度调整），避免类型不匹配报错。
• 隐藏排序字段：若不需要在最终结果中显示sort字段，可通过外层查询剔除：

  SELECT x, y, z
  FROM (
      SELECT 'bla' AS x, 'blabla' AS y, 'blab' AS z, 1 AS sort
      UNION ALL
      SELECT table1.x, table2.y, table3.z, 2 AS sort
      FROM table1
      LEFT JOIN table2 ON ...
      LEFT JOIN table3 ON ...
  ) combined
  ORDER BY sort
  

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