一个选择语句中的两个依赖的聚合函数是可能的吗？

Question

let me show You a simple table at first:

inr	party_id	ver
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

值'00006693'。我必须从VER列中获取最大值：在这种情况下，这将是“ 0025”。但是，如果列中的所有值都是相同的，则我必须从INR列获得最大值。在这种情况下，这将是“ 00024727”。结果最终应该看起来像：

INR	Party_ID	VER
00020222	00020107	0001
00006692	00006693	0025
0025 00006691	00006692	0018
00029732	00013671	0001

我的问题是，如何使用这两个max（）我不擅长分析问题。

我还将添加一个图像，其中描述了问题（我认为）更好，请基于它：

Answer 1

我可以提供两个选择。简单的解决方案 - 只需按party_id组中的“ ver desc，inr desc”排序：

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