select salesid, case when rn=1 then ltrim(orderprice) else '' end as orderprice from (select rn=row_number() over(partition by saleid,orderprice order by getdate()),* from tb) t
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([SALESID] varchar(5),[ORDERPRICE] int) insert [tb] select 'U0001',1000 union all select 'U0001',1000 union all select 'U0002',500
---查询--- select salesid, case when rn=1 then ltrim(orderprice) else '' end as orderprice from (select rn=row_number() over(partition by salesid,orderprice order by getdate()),* from tb) t---结果--- salesid orderprice ------- ------------ U0001 1000 U0001 U0002 500(3 行受影响)
--> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB]---->建表 create table [TB]([SALESID] varchar(5),[ORDERPRICE] int) insert [TB] select 'U0001',1000 union all select 'U0001',1000 union all select 'U0002',500 go with temp as ( SELECT rid=ROW_NUMBER() OVER(PARTITION BY SALESID ORDER BY SALESID DESC),* FROM [TB] a )select SALESID, case rid when 1 then temp.ORDERPRICE else '' end from temp --> 删除表格 --DROP TABLE [TB]
---测试数据--- 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 行受影响)
salesid,
case when rn=1 then ltrim(orderprice) else '' end as orderprice
from
(select rn=row_number() over(partition by saleid,orderprice order by getdate()),* from tb) t
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([SALESID] varchar(5),[ORDERPRICE] int)
insert [tb]
select 'U0001',1000 union all
select 'U0001',1000 union all
select 'U0002',500
---查询---
select
salesid,
case when rn=1 then ltrim(orderprice) else '' end as orderprice
from
(select rn=row_number() over(partition by salesid,orderprice order by getdate()),* from tb) t---结果---
salesid orderprice
------- ------------
U0001 1000
U0001
U0002 500(3 行受影响)
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]---->建表
create table [TB]([SALESID] varchar(5),[ORDERPRICE] int)
insert [TB]
select 'U0001',1000 union all
select 'U0001',1000 union all
select 'U0002',500
go
with temp as (
SELECT rid=ROW_NUMBER() OVER(PARTITION BY SALESID ORDER BY SALESID DESC),*
FROM [TB] a
)select SALESID, case rid when 1 then temp.ORDERPRICE else '' end from temp
--> 删除表格
--DROP TABLE [TB]
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 行受影响)