如果有多个相同的 ID，Where 子句

2021-09-10 00:00:00 tsql sql-server

我有下表:

ID | source | Name | Age | ... | ...
1  | SQL    | John | 18  | ... | ...
2  | SAP    | Mike | 21  | ... | ...
2  | SQL    | Mike | 20  | ... | ...
3  | SAP    | Jill | 25  | ... | ...

我希望每个 ID 都有一个记录.这背后的想法是，如果 ID 只出现一次(无论来源如何)，则将采用该记录.但是，如果一个 ID 有 2 条记录，那么以 SQL 作为源的记录将是这里使用的记录.

I want to have one record for each ID. The idea behind this is that if the ID comes only once (no matter the Source), that record will be taken. But, If there are 2 records for one ID, the one containing SQL as source will be the used record here.

所以，在这种情况下，结果将是:

So, In this case, the result will be:

ID | source | Name | Age | ... | ...
1  | SQL    | John | 18  | ... | ...
2  | SQL    | Mike | 20  | ... | ...
3  | SAP    | Jill | 25  | ... | ...

我是通过一个分区完成的(按 Source desc 排序)，但如果有一天会添加第三个源，这将无法正常工作.

I did this with a partition over (ordered by Source desc), but that wouldn't work well if a third source will be added one day.

还有其他选择/想法吗?

Any other options/ideas?

推荐答案

最简单的方法(在我看来)是使用带有排名功能的 CTE:

The easiest approach(in my opinion) is using a CTE with a ranking function:

with cte as
(
   select ID, source, Name, Age, ... , 
          rn = row_number() over (partition by ID order by case when source = 'sql'
                                                           then 0 else 1 end asc)
   from dbo.tablename
)
select ID, source, Name, Age, ...
from cte
where rn = 1

相关文章