Sql 根据是否存在值组合分组并 Select 行

发布于06月01日

我有一张PetsTable号桌:

Id	Type	key	value
1	"Cat"	10	5
1	"Cat"	9	2
2	"dog"	10	5
1	"Cat"	8	4
1	"Cat"	6	3
2	"dog"	8	4
2	"dog"	6	3
3	"Cat"	13	5
3	"Cat"	10	0
3	"Cat"	8	0

如何在满足以下条件的情况下将此数据从PetsTable插入到新表MyPets中:

按ID分组
只有在组中存在时才 Select 行(key=10和value=5)和(key=8和value=4)和(key=6和value=3)
如果存在密钥=9，则标记hasFee=1，否则标记hasFee=0

最终表格应如下所示:

Id	Type	hasFee
1	"Cat"	1
2	"dog"	0

推荐答案

一种方法是使用窗口函数来判断您的条件，然后可以使用CTE将其作为条件apply.

这将创建您想要的数据，然后将其插入到您 Select 的表中是很容易的.

create table Test (Id int, [Type] varchar(3), [Key] int, [Value] int);

insert into Test (Id, [Type], [Key], [Value])
values
(1, 'Cat', 10, 5),
(1, 'Cat', 9,  2),
(2, 'Dog', 10, 5),
(1, 'Cat', 8,  4),
(1, 'Cat', 6,  3),
(2, 'Dog', 8,  4),
(2, 'Dog', 6,  3),
(3, 'Cat', 13, 5),
(3, 'Cat', 10, 0),
(3, 'Cat', 8,  0);

with cte as (
  select *
    , sum(case when [Key] = 10 and [Value] = 5 then 1 else 0 end) over (partition by Id) Cond1
    , sum(case when [Key] = 8 and [Value] = 4 then 1 else 0 end) over (partition by Id) Cond2
    , sum(case when [Key] = 6 and [Value] = 3 then 1 else 0 end) over (partition by Id) Cond3
    , sum(case when [Key] = 9 then 1 else 0 end) over (partition by Id) HasFee
  from Test
)
select Id, [Type], HasFee
from cte
where Cond1 = 1 and Cond2 = 1 and Cond3 = 1
group by Id, [Type], HasFee;