我的要求修改成这样了,之前的不对了,请高手重指点:
SALESID prodid ORDERPRICE
U0001 L000001 1000
U0001 L000002 1000
U0002 L000003 500
变成这样:
SALESID prodid ORDERPRICE
U0001 L000001
U0001 L000002 1000
U0002 L000003 500意思是说,看SALESID 相同的话,那么 orderprice 只出现最后一条记录上就可以了
SALESID prodid ORDERPRICE
U0001 L000001 1000
U0001 L000002 1000
U0002 L000003 500
变成这样:
SALESID prodid ORDERPRICE
U0001 L000001
U0001 L000002 1000
U0002 L000003 500意思是说,看SALESID 相同的话,那么 orderprice 只出现最后一条记录上就可以了
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([SALESID] varchar(5),[prodid] varchar(7),[ORDERPRICE] int)
insert [tb]
select 'U0001','L000001',1000 union all
select 'U0001','L000002',1000 union all
select 'U0002','L000003',500
go
---查询---
select
salesid,
prodid,
case when rn=1 then ltrim(orderprice) else '' end as orderprice
from
(select rn=row_number() over(partition by salesid order by prodid desc),* from tb) t
order by 1,2---结果---
salesid prodid orderprice
------- ------- ------------
U0001 L000001
U0001 L000002 1000
U0002 L000003 500(3 行受影响)
,case when not exists(
select 1 from tb t2 where t2.salesid=t1.salesid and t2.prodid>t1.prodid
) then LTRIM(orderprice) else '' end
from tb t1