--> -->
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([日期] Datetime,[编号] int,[数量] int,[单价] int,[订单号] int,[采购数量] int,[采购价格] int)
Insert #T
select '2008-05-05',20,5,50,1234,2,30 union all
select '2008-05-08',20,5,50,1234,3,35
GoSelect
[日期],
[编号]=case when [编号]=(select top 1 [编号] from #T where [日期]<t.[日期] order by [日期] desc) then '' else rtrim([编号]) end,
[数量]=case when [数量]=(select top 1 [数量] from #T where [日期]<t.[日期] order by [日期] desc) then '' else rtrim([数量]) end,
[单价]=case when [单价]=(select top 1 [单价] from #T where [日期]<t.[日期] order by [日期] desc) then '' else rtrim([单价]) end,
[订单号]=case when [订单号]=(select top 1 [订单号] from #T where [日期]<t.[日期] order by [日期] desc) then '' else rtrim([订单号]) end,
[采购数量],[采购价格]
from
#T t
(2 個資料列受到影響)
日期 编号 数量 单价 订单号 采购数量 采购价格
----------------------- ------------ ------------ ------------ ------------ ----------- -----------
2008-05-05 00:00:00.000 20 5 50 1234 2 30
2008-05-08 00:00:00.000 3 35(2 個資料列受到影響)
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([日期] Datetime,[编号] int,[数量] int,[单价] int,[订单号] int,[采购数量] int,[采购价格] int)
Insert #T
select '2008-05-05',20,5,50,1234,2,30 union all
select '2008-05-08',20,5,50,1234,3,35
GoSelect
[日期],
[编号]=case when [编号]=(select top 1 [编号] from #T where [日期]<t.[日期] order by [日期] desc) then '' else rtrim([编号]) end,
[数量]=case when [数量]=(select top 1 [数量] from #T where [日期]<t.[日期] order by [日期] desc) then '' else rtrim([数量]) end,
[单价]=case when [单价]=(select top 1 [单价] from #T where [日期]<t.[日期] order by [日期] desc) then '' else rtrim([单价]) end,
[订单号]=case when [订单号]=(select top 1 [订单号] from #T where [日期]<t.[日期] order by [日期] desc) then '' else rtrim([订单号]) end,
[采购数量],[采购价格]
from
#T t
(2 個資料列受到影響)
日期 编号 数量 单价 订单号 采购数量 采购价格
----------------------- ------------ ------------ ------------ ------------ ----------- -----------
2008-05-05 00:00:00.000 20 5 50 1234 2 30
2008-05-08 00:00:00.000 3 35(2 個資料列受到影響)
drop table #T
Go
Create table #T([日期] Datetime,[编号] int,[数量] int,[单价] int,[订单号] int,[采购数量] int,[采购价格] int)
Insert #T
select '2008-05-05',20,5,50,1234,2,30 union all
select '2008-05-08',20,5,50,1234,3,35
GoSelect
[日期],
[编号]=case when not exists(select 1 from #T where [日期]>t.[日期]) then '' else rtrim([编号]) end,
[数量]=case when not exists(select 1 from #T where [日期]>t.[日期]) then '' else rtrim([数量]) end,
[单价]=case when not exists(select 1 from #T where [日期]>t.[日期]) then '' else rtrim([单价]) end,
[订单号]=case when not exists(select 1 from #T where [日期]>t.[日期]) then '' else rtrim([订单号]) end,
[采购数量],[采购价格]
from
#T t/*(影響 2 個資料列)日期 编号 数量 单价 订单号 采购数量 采购价格
------------------------------------------------------ ------------ ------------ ------------ ------------ ----------- -----------
2008-05-05 00:00:00.000 20 5 50 1234 2 30
2008-05-08 00:00:00.000 3 35(影響 2 個資料列)
*/