我需要做排名,如果值为负,则按 Asc 排序,如果为正,则按 Desc 排序
select
Itemcode,
isnull(sum(ss.DiscQty * ss.Cost),0) DescCost,
RANK()OVER(Partition by Itemcode order by
case when isnull(sum(ss.DiscQty * ss.Cost),0) < 0 THEN isnull(sum(ss.DiscrepancyQty * ss.Cost),0) END ASC,
case when isnull(sum(ss.DiscQty * ss.Cost),0) > 0 THEN isnull(sum(ss.DiscQty * ss.Cost),0) END DESC
) RANKS
from
ss
Group by
ItemNo
期待结果
ItemCode DiscQty Rank
===========================
111 -5000 1
121 -4500 2
222 10000 3
223 3000 4
但我得到所有的排名都是 1,
我只想
DiscQty
在Asc
DiscQty < 0
并在
Desc Order
中DiscQty
当"DiscQty> 0
你需要多个键,如下所示:
RANK() OVER (ORDER BY (CASE WHEN SUM(ss.DiscQty * ss.Cost) < 0 THEN SUM(ss.DiscQty * ss.Cost) ELSE 0 END) ASC,
SUM(ss.DiscQty * ss.Cost) DESC
) RANKS
你不需要PARTITION BY
.