根据给定条件从行集中选择行

Question

我在SQL Server上有一个表，它包含基于"Key1"列。下面是一些这样的集合的例子。

create  table myTable
(Key1 int,
Cond1 varchar(10),
Cond2 varchar(10),
Cond3 varchar(10),
Value int)
create table #temp
(Key1 int,
Value int)
insert into myTable values
(3, 'CT', 'IND' , 'INR', 6372),
(3, '', 'USA' , 'USD', 5732),
(3, '', '' , 'RUB', 7593),
(3, '', '' , '', 1045),
(5, '', '' , '', 9452),
(5, 'XU', 'ITA' , 'LIR', 6472),
(7, '', 'CAN' , 'CAD', 2845),
(7, '', '' , 'YEN', 8352)
| Key1 |  Cond1 |  Cond2  |  Cond3  |  Value  |
|------|--------|---------|---------|---------|
|   3  |   CT   |   IND   |   INR   |  6372   |
|   3  |        |   USA   |   USD   |  5732   |
|   3  |        |         |   RUB   |  7593   |
|   3  |        |         |         |  1045   |
|   5  |        |         |         |  9452   |
|   5  |   XU   |   ITA   |   LIR   |  6472   |
|   7  |        |   CAN   |   CAD   |  2845   |
|   7  |        |         |   YEN   |  8352   |

我需要为每个唯一的"Key1"选择键和值列

1. 如果三个cond1, cond2和cond3都存在，则选择相应的值。
2. 如果cond1为空，且存在cond2和cond3，则选择该值。
3. 如果cond1, cond2为空，且存在cond3，则选择相应的值。
4. 如果所有条件均为空，则选择相应的值。
5. 注意，只有当cond2和cond3存在时，才存在cond1。同样，只有当cond3已经存在时，cond2才存在。

所以最终输出应该如下所示

|  Key1  |  Value  |
|-------|---------|
|   3   |  6372   |
|   5   |  6472   |
|   7   |  2845   |

实现的一种方法是放置4个不同的select查询。

insert into #temp
select Key1, Value from myTable 
where cond1 <> '' and cond2 <> '' and cond3 <> ''
insert into #temp
select m.Key1, m.Value from myTable m  
left outer join #temp t
on m.Key1= t.Key1
where cond1 = '' and cond2 <> '' and cond3 <> ''
and t.Key1 is null
insert into #temp
select m.Key1, m.Value from myTable m  
left outer join #temp t
on m.Key1= t.Key1
where cond1 = '' and cond2 = '' and cond3 <> ''
and t.Key1 is null
insert into #temp
select m.Key1, m.Value from myTable m  
left outer join #temp t
on m.Key1= t.Key1
where cond1 = '' and cond2 = '' and cond3 = ''
and t.Key1 is null

但是这需要很多代码。我想知道是否有更好的方法来实现它?

Answer 1

If "平均空值然后使用逻辑。否则转换为<> '':

with data as (
select *,
row_number() over (partition by key1 order by
case
when cond1 is not null and cond2 is not null and cond3 is not null then 1
when cond2 is not null and cond3 is not null then 2
when cond3 is not null then 3
else 4
end) as rn
)
select * from data where rn = 1;

如果你同样需要知道第一个非空列和"condition"值不能重复，这两个也可以作为更短的版本:

case
when cond1 is not null then 1
when cond2 is not null then 2
when cond3 is not null then 3
else 4
end
case coalesce(cond1, cond2, cond3)
when cond1 then 1 when cond2 then 2 when cond3 then 3 else 4 end 

或者，如果您想依赖于null传播和字符串排序，则可能。这在回顾中很可能是不赞成的，但作为一个练习，它很有趣，也很有教育意义:

coalesce('1' + cond1, '2' + cond2, '3' + cond3, '4')

Answer 2

您可以使用distinct,first_value和case的组合来获得您想要的结果。

SELECT DISTINCT Key1, 
FIRST_VALUE([Value]) OVER(PARTITION BY Key1 ORDER BY 
CASE 
WHEN Cond1 <> '' THEN 1 -- can only happen if Cond2 and Cond3 have values 
WHEN Cond2 <> '' THEN 2 -- can only happen if Cond3 has a value 
WHEN Cond3 <> '' THEN 3 
ELSE 4
END
)
FROM myTable