如何将两个SQL查询合并为单查询并分别输出到不同列

UNION 是用来合并行记录的，它会把两个查询的结果按行拼接，列名以第一个查询的别名为准，所以你才会只看到Med列。要实现分独立列展示，可采用以下两种方法：

方法1：使用CASE条件判断（推荐，单表扫描效率更高） #

通过CASE WHEN在同一行中根据stars的范围分别填充Med和High列，不符合条件的位置用NULL占位：

SELECT
  CASE WHEN stars BETWEEN 2 AND 3 THEN stars ELSE NULL END AS Med,
  CASE WHEN stars BETWEEN 4 AND 5 THEN stars ELSE NULL END AS High
FROM business
WHERE city = 'Pittsburgh' AND is_open = 1
  AND (stars BETWEEN 2 AND 3 OR stars BETWEEN 4 AND 5);

预期输出： #

+-----+------+
| Med | High |
+-----+------+
| 2.0 | NULL |
| 2.5 | NULL |
| 3.0 | NULL |
| NULL| 4.0  |
| NULL| 4.5  |
| NULL| 5.0  |
+-----+------+

方法2：使用子查询+行号关联（适合结果集对齐场景） #

如果需要让两个范围的结果按顺序一一对应展示，可以用子查询生成带行号的数据集，再通过行号关联：

WITH med_data AS (
  SELECT stars, ROW_NUMBER() OVER (ORDER BY stars) AS rn
  FROM business
  WHERE city = 'Pittsburgh' AND is_open = 1 AND stars BETWEEN 2 AND 3
),
high_data AS (
  SELECT stars, ROW_NUMBER() OVER (ORDER BY stars) AS rn
  FROM business
  WHERE city = 'Pittsburgh' AND is_open = 1 AND stars BETWEEN 4 AND 5
)
SELECT m.stars AS Med, h.stars AS High
FROM med_data m
FULL OUTER JOIN high_data h ON m.rn = h.rn;

预期输出（两边数量一致时）： #

+-----+------+
| Med | High |
+-----+------+
| 2.0 | 4.0  |
| 2.5 | 4.5  |
| 3.0 | 5.0  |
+-----+------+

FULL OUTER JOIN能保证任意一边存在的记录都不会被遗漏。

内容的提问来源于stack exchange，提问作者Vladimir Honc-Lamadrid