我有以下表名称JobTitle
JobID LanaguageID ----------------- 1 1 1 2 1 3 2 1 2 2 3 4 4 5 5 2
我正在从表格中选择所有记录,除了重复的JobID的计数> 1.我只从重复的JobID中选择一个记录/第一行.
现在我将LanguageID作为参数传递给存储过程,我想为该languageID选择重复的JobID以及其他记录.
如果我已将languageID作为1传递,则输出应如下所示
JobID LanaguageID ----------------- 1 1 2 1 3 4 4 5 5 2
我尝试过使用以下查询.
with CTE_RN as
(
SELECT ROW_NUMBER() OVER(PARTITION BY JobTitle.JobID ORDER BY JobTitle.JobTitle) AS RN
FROM JobTitle
INNER JOIN JobTitle_Lang
ON JobTitle.JobTitleID = JobTitle_Lang.JobTitleID
)
解决方法
with CTE_RN as
(
SELECT
JobID,LanaguageID,ROW_NUMBER() OVER(PARTITION BY JobTitle.JobID ORDER BY JobTitle.JobTitle) AS RN
FROM JobTitle
INNER JOIN JobTitle_Lang ON JobTitle.JobTitleID = JobTitle_Lang.JobTitleID
)
select
from CTE_RN
where RN = 1 or LanguageID = @LanguageID
更新
简化了一点(加入删除),但你会明白：
declare @LanguageID int = 2
;with cte_rn as
(
select
JobID,LanguageID,row_number() over(
partition by JobTitle.JobID
order by
case when LanguageID = @LanguageID then 0 else 1 end,LanguageID
) as rn
from JobTitle
)
select *
from cte_rn
where rn = 1
