update 表
set f_oilnameid=3
from 表 t
where (select count(1) from 表 where f_businessdate=t.f_businessdate and f_id<=t.f_id order by f_id)=3
set f_oilnameid=3
from 表 t
where (select count(1) from 表 where f_businessdate=t.f_businessdate and f_id<=t.f_id order by f_id)=3
set
f_oilnameid = 3
from
表 a
where
(select count(*) from 表 where f_businessdate=a.f_businessdate and f_id<a.f_id) = 2
(
f_id int,
f_oilnameid int,
f_businessdate varchar(10)
)
insert @tb
select 1,1,'2005-08-25' union
select 2,2,'2005-08-25' union
select 3,2,'2005-08-25' union
select 4,1,'2005-08-24' union
select 5,2,'2005-08-24' union
select 6,2,'2005-08-24' --测试
update @tb
set f_oilnameid=3
from @tb t
where (select count(1) from @tb where f_businessdate=t.f_businessdate and f_id<=t.f_id)=3--查看
select * from @tb
--结果
/*
f_id f_oilnameid f_businessdate
----------- ----------- --------------
1.00 1.00 2005-08-25
2.00 2.00 2005-08-25
3.00 3.00 2005-08-25
4.00 1.00 2005-08-24
5.00 2.00 2005-08-24
6.00 3.00 2005-08-24(所影响的行数为 6 行)
*/
create table #t(f_id int,f_oilnameid int,f_businessdate datetime)
insert into #t select 1,1,'2005-08-25'
insert into #t select 2,2,'2005-08-25'
insert into #t select 3,2,'2005-08-25'
insert into #t select 4,1,'2005-08-24'
insert into #t select 5,2,'2005-08-24'
insert into #t select 6,2,'2005-08-24'
--执行更新语句
update a
set
f_oilnameid = 3
from
#t a
where
(select count(*) from #t where f_businessdate=a.f_businessdate and f_id<a.f_id) = 2
--输出更新结果
select * from #t
/*
f_id f_oilnameid f_businessdate
---- ----------- -----------------------
1 1 2005-08-25 00:00:00.000
2 2 2005-08-25 00:00:00.000
3 3 2005-08-25 00:00:00.000
4 1 2005-08-24 00:00:00.000
5 2 2005-08-24 00:00:00.000
6 3 2005-08-24 00:00:00.000
*/
能给我解释一下
(select count(*) from #t where f_businessdate=a.f_businessdate and f_id<a.f_id) = 2
这个的意思吗?
我看不懂and f_id<a.f_id 这是什么意思?
能给我解释一下
(select count(*) from #t where f_businessdate=a.f_businessdate and f_id<a.f_id) = 2
这个的意思吗?
我看不懂and f_id<a.f_id 这是什么意思?
-------------不好意思,本人来晚了,我就帮人扫尾工作吧,记得结贴是不要忘了俺 :)
select count(*) from #t where f_businessdate=a.f_businessdate and f_id<a.f_id--统计同一天(
f_businessdate=a.f_businessdate )中记录号(f_id)排名第三的(f_id<a.f_id )=2
(select count(*) from #t where f_businessdate=a.f_businessdate and f_id<a.f_id) = 2这条语句怎么就能找到了这一天第三条了呢?
(
f_id int,
f_oilnameid int,
f_businessdate varchar(10)
)
insert t
select 1,1,'2005-08-25' union
select 2,2,'2005-08-25' union
select 3,2,'2005-08-25' union
select 4,1,'2005-08-24' union
select 5,2,'2005-08-24' union
select 6,2,'2005-08-24'update t set f_oilnameid = 3 where f_id in (select max(f_id) from t group by f_businessdate)select * from t --输出更新结果
/*
f_id f_oilnameid f_businessdate
---- ----------- -----------------------
1 1 2005-08-25 00:00:00.000
2 2 2005-08-25 00:00:00.000
3 3 2005-08-25 00:00:00.000
4 1 2005-08-24 00:00:00.000
5 2 2005-08-24 00:00:00.000
6 3 2005-08-24 00:00:00.000
*/drop table t
set f_oilnameid=3
from 表 a
where f_id in(
select top 3 f_id from 表 where f_businessdate=a.f_businessdate)
set f_oilnameid=3
from 表 t
where (select count(1) from 表 where f_businessdate=t.f_businessdate and f_id<=t.f_id order by f_id)=3