我有一張如下表：
結果集：
| Candidate_Key | Job_Key |
| --- | --- |
| 913361 | NULL |
| 913361 | 13566 |
| 913361 | 13569 |
| 747197 | NULL |
| 656363 | NULL |
| 656363 | 12266 |
| 143143 | NULL |
所需的結果集
| Candiate_Key | Job_Key |
| --- | --- |
| 913361 | 13566 |
| 913361 | 13569 |
| 747197 | NULL |
| 656363 | 12266 |
| 143143 | NULL |
如果 Candidate_Key 具有 Job_Key 值（非 NULL），則需要檢索唯一的 Job_Key（根據 Candidate_Key [PARTITION Candidate_Key ]）.........&
如果 Candidate_Key 只有 NULL 值，則需要檢索 First Candidate_Key 并且它是相應的 Job_Key，即使它是 NULL
uj5u.com熱心網友回復：
使用 union 完成此操作的另一種方法（盡管 Michal 的答案可能更有效）：
declare @tbl table (
Candidate_Key varchar(10),
Job_Key varchar(10));
insert into @tbl
values
(null, null),
('913361', null),
('913361', '13566'),
('913361', '13569'),
('747197', null),
('656363', null),
('656363', '12266'),
('143143', null);
with cte as (
select Candidate_Key, Job_Key
from @tbl
where Job_Key is not null
)
--get candidates with non-null job keys
select t.*
from @tbl as t
inner join cte as c
on t.Candidate_Key = c.Candidate_Key
where t.Job_Key is not null
union
--get candidates with only null job keys
select distinct t.*
from @tbl as t
left join cte as c
on t.Candidate_Key = c.Candidate_Key
where c.Candidate_Key is null
uj5u.com熱心網友回復：
這是使用視窗函式的示例查詢，我將測驗資料擴展為還包含NULL單個候選鍵的多個值：
declare @tbl table (Candidate_Key int, Job_Key int);
insert into @tbl values
(913361, NULL ),
(913361, 13566),
(913361, 13569),
(747197, NULL ),
(747197, NULL ),
(656363, NULL ),
(656363, 12266),
(143143, NULL );
select
Candidate_Key,
Job_Key
from (
select
Candidate_Key,
Job_Key,
row_number() over (partition by Candidate_Key order by Candidate_Key) rn,
sum(case when Job_Key is null then 0 else 1 end) over (partition by Candidate_Key) notNullCnt
from @tbl
) a
where Job_Key is not null or (notNullCnt = 0 and rn = 1)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/347753.html
標籤：sql sql-server sql-server-2016
上一篇：Sqlserver如何獲取只存在一次、2到5次、大于5次的兩列值組合
下一篇：如何計算列中的類