日期:2014-05-16 浏览次数:20776 次
--drop table a
create table a(cardname varchar(20),itemname varchar(10),quantity int,yf int,date datetime)
insert into a
select 'supplier', 'test', 10 ,24 ,'2013-2-22' union all
select 'supplier', 'test', 20 ,24 ,'2013-2-22' union all
select 'supplier', 'test', 30 ,36 ,'2013-2-24' union all
select 'supplier', 'test', 40 , 48 ,'2013-2-25'
go
select cardname,itemname,quantity,
case when rownum >= 2 then 0 else yf end as yf,
date
from
(
select *,
ROW_NUMBER() over(partition by yf,date order by getdate()) rownum
from a
)a
/*
cardname itemname quantity yf date
supplier test 10 24 2013-02-22 00:00:00.000
supplier test 20 0 2013-02-22 00:00:00.000
supplier test 30 36 2013-02-24 00:00:00.000
supplier test 40 48 2013-02-25 00:00:00.000
*/