如何将两个SQL查询结果合并为并列两列展示？

直接用GROUP BY就能一步得到你要的结果，比分开查询再合并更高效可靠：

SELECT column_name, COUNT(column_name) AS Amount
FROM owner_name.table_name
GROUP BY column_name
ORDER BY column_name;

为什么这个写法可行？ #

• GROUP BY column_name会自动对column_name去重，把相同值的行归为一组
• COUNT(column_name)统计每组内的行数，就是你要的重复次数
• 最后按column_name排序，和你原来两个查询的排序逻辑一致

如果你一定要基于原来的两个查询来合并（不推荐，效率更低），可以给每个查询加行号，通过行号关联：

WITH cte1 AS (
    SELECT DISTINCT column_name, ROW_NUMBER() OVER (ORDER BY column_name) AS rn
    FROM owner_name.table_name
),
cte2 AS (
    SELECT DISTINCT Amount, ROW_NUMBER() OVER (ORDER BY Amount) AS rn
    FROM (
        SELECT COUNT(column_name) OVER (PARTITION BY column_name) AS Amount
        FROM owner_name.table_name
    ) t
)
SELECT c1.column_name, c2.Amount
FROM cte1 c1
JOIN cte2 c2 ON c1.rn = c2.rn
ORDER BY c1.column_name;

注意：这种方式依赖两个查询的行号完全对应，一旦排序逻辑有差异就会出错，所以优先用第一种GROUP BY的写法。

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