两个依赖的聚合函数在一个SELECT语句中，这是可能的吗?

Question

让我先给你看一个简单的表格:

版本000100250006000100000018000000000000000000000001000297320001367100010000

Answer 1

我可以提供两种选择。简单的解决方案-只需按"类型"排序，"类型"排序即可。在party_id组内:

with test_data as (
select '00020222' inr,    '00020107' party_id,    '0001' ver union
select '00006692',    '00006693', '0025' union
select '00021768',    '00006693', '0006' union
select '00024726',    '00006693', '0001' union
select '00024727',    '00006693', '0000' union
select '00006691',    '00006692', '0018' union
select '00021949',    '00006692', '0000' union
select '00024728',    '00006692', '0000' union
select '00034567',    '00019734', '0022' union
select '00064657',    '00019734', '0022'
)
select
r.inr, r.party_id, r.ver
from 
(  
select 
t.*, 
row_number() over(partition by t.party_id order by t.ver desc, t.inr desc) rn
from
test_data t
) r
where
r.rn = 1

第二个解决方案更冗长，但直接实现您的逻辑:

with test_data as (
select '00020222' inr,    '00020107' party_id,    '0001' ver union
select '00006692',    '00006693', '0025' union
select '00021768',    '00006693', '0006' union
select '00024726',    '00006693', '0001' union
select '00024727',    '00006693', '0000' union
select '00006691',    '00006692', '0018' union
select '00021949',    '00006692', '0000' union
select '00024728',    '00006692', '0000' union
select '00034567',    '00019734', '0022' union
select '00064657',    '00019734', '0022'
)
select 
r.inr, r.party_id, r.ver
from
(  
select 
t.*,
case when count(distinct t.ver) over(partition by t.party_id) == 1 then 1 else 0 end is_all_ver_same,
row_number() over(partition by t.party_id order by t.ver desc) max_ver,
row_number() over(partition by t.party_id order by t.inr desc) max_inr
from 
test_data t
) r   
where 
(r.is_all_ver_same = 1 and r.max_inr = 1) or (r.is_all_ver_same = 0 and r.max_ver = 1)

Answer 2

我认为这在一个选择语句中是不可行的，因为窗口函数有其局限性，但您需要的可以通过包含ROW_NUMBER()的子查询来解决排序功能:

if object_id(N'tempdb..#Temp') is not null 
drop table #Temp
create table #Temp (
inr int null,
party_id int null,
ver int null
)
insert into #Temp
(
inr,
party_id,
ver
)
values
(00020222,  00020107,   0001),
(00006692,  00006693,   0025),
(00021768,  00006693,   0006),
(00024726,  00006693,   0001),
(00024727,  00006693,   0000),
(00006691,  00006692,   0018),
(00021949,  00006692,   0000),
(00024728,  00006692,   0000),
(00024928,  00006692,   0000),
(00013670,  00013671,   0000),
(00027865,  00013671,   0000),
(00029716,  00013671,   0001),
(00029732,  00013671,   0001),
(00029749,  00013671,   0000)
select 
inr,
party_id,
ver
from
(
select
row_number() over (partition by party_id order by ver desc, inr desc) as rn,
*
from #Temp
) a
where rn = 1