sql server 2005table结构如下
id int
checkdate datatime
flag intid checkdate flag
1 2005/01/01 null
1 2004/01/01 null
1 2001/01/01 null
2 2005/01/01 null
2 2004/01/01 null
3 2005/01/01 null
3 2000/01/01 null要对flag 字段赋值,要求是相同的id如果checkdate去年有数据的话flag 为 1 否则为0
要求结果
id checkdate flag
1 2005/01/01 1
1 2004/01/01 0
1 2001/01/01 0
2 2005/01/01 1
2 2004/01/01 0
3 2005/01/01 0
3 2000/01/01 0
用一条sql实现。各位大侠帮忙。
id int
checkdate datatime
flag intid checkdate flag
1 2005/01/01 null
1 2004/01/01 null
1 2001/01/01 null
2 2005/01/01 null
2 2004/01/01 null
3 2005/01/01 null
3 2000/01/01 null要对flag 字段赋值,要求是相同的id如果checkdate去年有数据的话flag 为 1 否则为0
要求结果
id checkdate flag
1 2005/01/01 1
1 2004/01/01 0
1 2001/01/01 0
2 2005/01/01 1
2 2004/01/01 0
3 2005/01/01 0
3 2000/01/01 0
用一条sql实现。各位大侠帮忙。
insert @t select
1,'2005/01/01',null union select
1,'2004/01/01',null union select
1,'2001/01/01',null union select
2,'2005/01/01',null union select
2,'2004/01/01',null union select
3,'2005/01/01',null union select
3,'2000/01/01',null update a
set flag = case when exists(select 1 from @t where id = a.id and datediff(yy,checkdate,a.checkdate) = 1) then 1
else 0 end
from @t aselect * from @t
/*id checkdate flag
----------- ------------------------------------------------------ -----------
1 2001-01-01 00:00:00.000 0
1 2004-01-01 00:00:00.000 0
1 2005-01-01 00:00:00.000 1
2 2004-01-01 00:00:00.000 0
2 2005-01-01 00:00:00.000 1
3 2000-01-01 00:00:00.000 0
3 2005-01-01 00:00:00.000 0*/
insert @t select
1,'2005/01/01',null union select
1,'2004/01/01',null union select
1,'2001/01/01',null union select
2,'2005/01/01',null union select
2,'2004/01/01',null union select
3,'2005/01/01',null union select
3,'2000/01/01',null update a
set flag = case when exists(select 1 from @t where id = a.id and datediff(yy,checkdate,a.checkdate) = 1) then 1
else 0 end
from @t aselect *
from @t
order by id,checkdate desc
/*id checkdate flag
----------- ------------------------------------------------------ -----------
1 2005-01-01 00:00:00.000 1
1 2004-01-01 00:00:00.000 0
1 2001-01-01 00:00:00.000 0
2 2005-01-01 00:00:00.000 1
2 2004-01-01 00:00:00.000 0
3 2005-01-01 00:00:00.000 0
3 2000-01-01 00:00:00.000 0(所影响的行数为 7 行)*/
Update a
set flag=case when exists (select 1 from tab where id=a.id and year(checkdate)=year(a.checkdate)-1) then 1 else 0 end
from tab a
create table Test
(
id int,
checkdate Datetime,
flag int
)insert Test select 1,'2005/01/01',null
insert Test select 1,'2004/01/01',null
insert Test select 1,'2001/01/01',null
insert Test select 2,'2005/01/01',null
insert Test select 2,'2004/01/01',null
insert Test select 3,'2005/01/01',null
insert Test select 3,'2000/01/01',null
update Test set flag=case when (select 1 from Test where year(A.checkdate)-1=year(checkdate) and A.id=id)=1 then 1 else 0 end
from Test Aselect * from Test
(
id int,
checkdate Datetime,
flag int
)insert Test select 1,'2005/01/01',null
insert Test select 1,'2004/01/01',null
insert Test select 1,'2001/01/01',null
insert Test select 2,'2005/01/01',null
insert Test select 2,'2004/01/01',null
insert Test select 3,'2005/01/01',null
insert Test select 3,'2000/01/01',nullupdate test set flag=case when (select count(1) from test where id=a.id and year(checkdate)=year(a.checkdate)-1)>0 then 1 else 0 end from test aselect * from test
id checkdate flag
----------- ----------------------- -----------
1 2005-01-01 00:00:00.000 1
1 2004-01-01 00:00:00.000 0
1 2001-01-01 00:00:00.000 0
2 2005-01-01 00:00:00.000 1
2 2004-01-01 00:00:00.000 0
3 2005-01-01 00:00:00.000 0
3 2000-01-01 00:00:00.000 0(7 行受影