如何为唯一 id 组合返回更大的周期值?

Question

如果paid_amount该section_id和store_id组合的period_id值较大，则存储将获得checked列的 Y 值。以下是我根据上面列出的条件创建的查询：

select section_id, store_id, paid_amount, period_id, secttion_id + store_id as unique_id
from store_data 
where paid_amount is not null and paid_amount > 0
order by store_id, paid_amount desc;

上面的查询产生以下数据：

section_id  store_id  paid_amount  period_id  unique_id
3604        30545     10000.00     3          30545
3604        30545     5000.00      2          30545
5967        32105     8470.00      3          38072
5967        32105     8470.00      2          38072
1367        46144     23456.00     2          47511
1367        46144     23456.00     3          47511
1367        46144     23456.00     4          47511
1367        46144     23456.00     5          47511
1376        72181     19975.00     2          73557

如果有多个数据，我只需要获取一行数据。例如，对于unique_id30545我只想要数量较大的行。在这种情况下，应该与10000.00的paid_amount行。如果行只有一条记录，我只需要该行。有没有一种简单的方法可以在Sybase实现这一目标？

Answer 1

由于Sybase不支持窗口函数，这里有一种方法：

select s1.* from store_data s1
join (
select unique_id , max(paid_amount) paid_amount
from store_data s2
where s2.paid_amount is not null and s2.paid_amount > 0
group by s2.unique_id
) s2
on s1.unique_id = s2.unique_id
and s1.paid_amount = s2.paid_amount

但是，如果您仍然有重复项，则可以缩小条件：

select s1.* from store_data s1
join (
select unique_id , max(paid_amount) paid_amount, max(period) period
from store_data s2
where s2.paid_amount is not null and s2.paid_amount > 0
group by s2.unique_id
) s2
on s1.unique_id = s2.unique_id
and s1.paid_amount = s2.paid_amount
and s1.period_id = s2.period_id