問題描述
我有下表:
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
這篇關于如果有多個相同的 ID，Where 子句的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！