针对你提出的三个筛选条件，我这里提供两种实用的SQL解决方案，都能完美匹配你的预期结果：

方法一：使用窗口函数ROW_NUMBER()（推荐） #

这种方法逻辑清晰，扩展性强，是处理这类分组筛选场景的常用方案：

WITH ranked_devices AS (
    SELECT 
        d.device_id,
        d.name,
        d.amount,
        d.status,
        -- 按device_id分组，优先将active标记为序号1，非active按任意规则排序
        ROW_NUMBER() OVER (
            PARTITION BY d.device_id 
            ORDER BY CASE WHEN d.status = 'active' THEN 0 ELSE 1 END, d.amount
        ) AS rn
    FROM Device d WITH(NOLOCK)
)
SELECT device_id, name, amount, status
FROM ranked_devices
WHERE rn = 1;

逻辑拆解： #

• PARTITION BY d.device_id：把数据按device_id拆分成独立的分组；
• ORDER BY CASE WHEN d.status = 'active' THEN 0 ELSE 1 END：让每个组里的active记录排在最前面，序号自动设为1；
• 后面的d.amount是兜底规则——当组内没有active记录时，按amount排序选第一条，你也可以换成name或者其他字段，只要能确保选中任意一条即可；
• 最后筛选rn = 1的记录，刚好覆盖所有需求：
  1. 重复device_id且含active：只保留active的那条；
  2. 重复device_id无active：选排序后的第一条；
  3. 无重复的device_id：组内仅一条，自然被选中。

方法二：使用EXISTS子查询 #

如果你更习惯子查询的写法，也可以用这种方式实现：

-- 第一步：选出所有active状态的记录
SELECT device_id, name, amount, status
FROM Device d WITH(NOLOCK)
WHERE status = 'active'

UNION ALL

-- 第二步：选出那些没有active记录的device_id的任意一条
SELECT device_id, name, amount, status
FROM Device d1 WITH(NOLOCK)
WHERE NOT EXISTS (
    SELECT 1 
    FROM Device d2 
    WHERE d2.device_id = d1.device_id AND d2.status = 'active'
)
AND NOT EXISTS (
    SELECT 1 
    FROM Device d3 
    WHERE d3.device_id = d1.device_id AND d3.name < d1.name -- 这里用name排序选第一条，可按需替换字段
);

逻辑拆解： #

• 第一部分直接筛选所有active记录，满足条件1的需求；
• 第二部分先过滤出没有active记录的device_id，再通过第二个EXISTS确保每个device_id只选一条（这里选name最小的，你可以换成amount或其他字段）；
• 用UNION ALL合并结果，避免重复且效率更高。

两种方法最终都会得到你预期的结果集：

device_id name amount status
100 abc 50 active
200 xyz 150 inactive
300 pqr 100 active
400 aaa 10 active
500 hhh 30 inactive

内容的提问来源于stack exchange，提问作者to-find