多表OS数据获取唯一主机名对应最低优先级操作系统的SQL实现

方法1：窗口函数（推荐，通用且高效） #

利用ROW_NUMBER()窗口函数给每个主机的记录按优先级排序，直接取优先级最低的第一条记录，优先级相同时会随机选中一条，符合需求。

适用于支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等），后续扩展表时只需在合并数据的部分追加UNION ALL语句即可。

WITH combined_data AS (
    -- 合并所有OS_Data表的数据
    SELECT Hostname, Operating_System, OS_Priority FROM OS_Data1
    UNION ALL
    SELECT Hostname, Operating_System, OS_Priority FROM OS_Data2
    UNION ALL
    SELECT Hostname, Operating_System, OS_Priority FROM OS_Data3
    -- 后续加表直接追加UNION ALL ...
),
ranked_records AS (
    SELECT 
        Hostname,
        Operating_System,
        OS_Priority,
        -- 按主机分组，优先级升序编号，最低优先级为1
        ROW_NUMBER() OVER (PARTITION BY Hostname ORDER BY OS_Priority ASC) AS rank_num
    FROM combined_data
)
-- 只取每个主机的第一条（最低优先级）记录
SELECT Hostname, Operating_System, OS_Priority
FROM ranked_records
WHERE rank_num = 1;

方法2：子查询关联（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以先通过子查询获取每个主机的最低优先级，再关联回合并后的数据集筛选对应记录。

SELECT c.Hostname, c.Operating_System, c.OS_Priority
FROM (
    -- 合并所有表数据
    SELECT Hostname, Operating_System, OS_Priority FROM OS_Data1
    UNION ALL
    SELECT Hostname, Operating_System, OS_Priority FROM OS_Data2
    UNION ALL
    SELECT Hostname, Operating_System, OS_Priority FROM OS_Data3
) c
-- 关联每个主机的最低优先级数据
INNER JOIN (
    SELECT Hostname, MIN(OS_Priority) AS min_priority
    FROM (
        SELECT Hostname, OS_Priority FROM OS_Data1
        UNION ALL
        SELECT Hostname, OS_Priority FROM OS_Data2
        UNION ALL
        SELECT Hostname, OS_Priority FROM OS_Data3
    ) t
    GROUP BY Hostname
) p ON c.Hostname = p.Hostname AND c.OS_Priority = p.min_priority
-- 去重（处理同优先级多OS的情况）
GROUP BY c.Hostname, c.Operating_System, c.OS_Priority;

关键注意事项 #

• 优先用UNION ALL而非UNION：UNION会自动去重，增加额外性能开销；而UNION ALL仅合并数据，后续排序筛选逻辑不受重复记录影响，效率更高。
• 优先级相同的处理：两种方法都会自动任选一条同优先级的记录，若需要固定选中某一条（比如按操作系统名称排序），只需在ORDER BY后追加额外字段，例如ORDER BY OS_Priority ASC, Operating_System ASC。

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