如何在SQL Server中应用基于重复记录的条件

Question

我们有一个名为device的表，数据如下所示:

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

我们必须修改一个已有的查询，一个简单的选择查询，以满足以下条件。

1. 如果我们有重复的设备id(例如:第1行和第2行)，其中至少有一个是活动的，那么我们必须只选择活动的设备id。
2. 如果我们有重复的设备id，没有任何活动设备id(例如:第7行和第8行)，那么我们可以选择其中的任何设备id。
3. 如果我们没有任何重复的设备id(例如:第3行和第4行)，那么我们必须选择所有设备id。

所以最终的结果集应该如下所示，

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

我们现有的查询如下，

select d.DEVICE_ID, d.name, d.amount
from Device d with(nolock)

Answer 1

您可以使用row_number()为每个组选择特定的行:

with d as (
select *, Row_Number() over(partition by device_id order by status) rn
from device
)
select device_id, name, amount, status
from d
where rn=1