如何用Hive SQL合并同表不同过滤条件的查询结果

咱们先来明确你的需求：你从同一张mailing1表中分别查询了layer='db_gold'和layer='db_insight'的两组数据，想要把它们按行交替输出（先一条gold，再一条insight，依次循环），而普通的UNION只能把其中一组的所有结果放在前面，另一组放在后面，所以没法满足你的要求。

解决方案：用行号关联+联合排序 #

核心思路是给两组数据分别添加行号，通过行号把两组中对应的记录关联起来，再重新排列输出。具体的Hive SQL如下：

WITH gold_records AS (
    -- 给db_gold的记录按value排序并添加行号
    SELECT layer, value,
           ROW_NUMBER() OVER (ORDER BY value) AS row_num
    FROM mailing1
    WHERE layer = 'db_gold'
),
insight_records AS (
    -- 给db_insight的记录按value排序并添加行号
    SELECT layer, value,
           ROW_NUMBER() OVER (ORDER BY value) AS row_num
    FROM mailing1
    WHERE layer = 'db_insight'
)
-- 合并并排序得到交替结果
SELECT layer, value
FROM (
    -- 先取出每组行号对应的gold记录
    SELECT gr.layer, gr.value, gr.row_num
    FROM gold_records gr
    JOIN insight_records ir ON gr.row_num = ir.row_num
    UNION ALL
    -- 再取出每组行号对应的insight记录
    SELECT ir.layer, ir.value, ir.row_num
    FROM gold_records gr
    JOIN insight_records ir ON gr.row_num = ir.row_num
) combined_results
-- 先按行号分组，同一行号下先显示db_gold再显示db_insight
ORDER BY row_num, layer DESC;

代码解释 #

1. CTE定义行号：用两个CTE分别给两组数据按value排序并添加行号，确保两组数据的顺序和你单独查询时一致。
2. 关联+联合：通过行号将两组数据关联（保证每个gold记录对应一个insight记录），再用UNION ALL把关联后的gold和insight记录合并成一个数据集。
3. 排序输出：最后按行号排序，同一行号下通过layer DESC让db_gold排在db_insight前面，这样就实现了你要的交替输出效果。

验证结果 #

用你提供的测试数据执行这个SQL，会得到完全符合预期的输出：

db_gold 400
db_insight 100
db_gold 500
db_insight 200
db_gold 600
db_insight 300

如果两组数据的记录数不一致（比如一组多一组少），可以把JOIN改成LEFT JOIN或者RIGHT JOIN，并处理NULL值的情况，不过你的示例中两组记录数相同，直接用JOIN就可以啦。

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