22,207
社区成员
发帖
与我相关
我的任务
分享
with tb(运费,标识,订单号) as
(
select 5,'S',1001 union all
select 5,'S',1001 union all
select 10,'S',1002 union all
select 10,'S',1003 union all
select 10,'Y',1004 union all
select 12,'Y',1005 union all
select 12,'Y',1005 union all
select 15,'Y',1006
),
tb1 as
(
select *,ROW_NUMBER()over(PARTITION by 标识,订单号 order by getdate())rn from tb
)
select sum(case when 标识='S' then 运费 when 标识='Y' and rn=1 then 运费 end)'合计',
SUM(case when 标识='S' then 运费 end)'S合计',
SUM(case when 标识='Y' and rn=1 then 运费 end)'Y合计' from tb1
合计 S合计 Y合计
----------- ----------- -----------
67 30 37