sql - 每个组的第二个最大值 - SQL

Question

我有这个数据集:

shopID    supplier    supply_count
1         a           12
2         b           12
2         f           12
2         e           10
3         b           5
3         a           2
4         f           15
4         c           11

我使用这个查询来获得每个组的第二个最高值:

select s1.shopId, max(s1.supply_count)
from supply s1
where supply_count NOT IN (
   select max(supply_count) 
   from supply s2
   where s1.shopId = s2.shopId
)
group by s1.shopId

我得到的结果是:

shopID   supply_count
2        10
3        2
4        11

所需的输出是:
(如果没有第二高，我想呈现最高的)

shopID   supply_count
1        12
2        10
3        2
4        11

基于这个问题:
Retrieve 2nd highest count by each group

Answer 1

通过计算每个 ShopId 的行数，您可以获得所需的输出，以包含每个 shopId 只有一行的值。
根据一些更多样化的数据和评论，我相信以下内容应该会给出预期的结果:

with c as (
    select *,
        case when dense_rank() over(partition by shopid order by supply_count desc)=2 
            or Min(supply_count) over(partition by shopid)
              =Max(supply_count) over(partition by shopid) 
        then 1 else 0 end as valid
    from t
)
select distinct shopId, supply_count
from c
where valid=1

示例 DBFiddle with some more varied data