select ID,AnDate, (case when exists(select 1 from 表 where ID = a.ID and AnDate<a.AnDate) then 0 else 1 end) as AnStatus from 表 a
select id,andate,(case when exists (select * from test t1 where year(t1.andate)=year(t.andate) having t1.min(andate)=t2.andate) then 1 else 0 end) as anstatus from test t
update table set AnStatus = 1 where AnDate in (select min(AnDate) from table group by AnDate)
select id,andate,(case when exists (select * from test t1 where id=t.id and year(t1.andate)=year(t.andate) having min(andate)=t.andate) then 1 else 0 end) as anstatus from test t
测试如下 create table test(id int,andate datetime) insert into test values(99101, '2002-11-24 0:00') insert into test values(99101 ,'2003-11-15 0:00') insert into test values(99101, '2003-11-29 0:00') insert into test values(99101 ,'2003-12-6 0:00') insert into test values(99101, '2003-12-13 0:00') insert into test values(99101 ,'2003-12-20 0:00') insert into test values(99101, '2003-12-29 0:00') insert into test values(99101 ,'2004-1-3 0:00') insert into test values(99101, '2004-1-10 0:00') insert into test values(99101 ,'2004-1-17 0:00') insert into test values(99101, '2004-1-24 0:00') insert into test values(99101 ,'2004-1-31 0:00') insert into test values(99101, '2004-2-28 0:00') insert into test values(99101 ,'2004-3-3 0:00') insert into test values(99101 ,'2004-3-6 0:00') insert into test values(99102, '2002-11-24 0:00') insert into test values(99102 ,'2003-11-15 0:00') insert into test values(99102, '2003-11-29 0:00') insert into test values(99102 ,'2003-12-6 0:00') insert into test values(99102, '2003-12-13 0:00') insert into test values(99102 ,'2003-12-20 0:00') insert into test values(99102, '2003-12-29 0:00') insert into test values(99102 ,'2004-1-3 0:00') insert into test values(99102, '2004-1-10 0:00') insert into test values(99102 ,'2004-1-17 0:00') insert into test values(99102, '2004-1-24 0:00') insert into test values(99102 ,'2004-1-31 0:00') insert into test values(99102, '2004-2-28 0:00') insert into test values(99102 ,'2004-3-3 0:00') insert into test values(99102 ,'2004-3-6 0:00') go select id,andate,(case when exists (select * from test t1 where id=t.id and year(t1.andate)=year(t.andate) having min(andate)=t.andate) then 1 else 0 end) as anstatus from test t结果: 99101 2002-11-24 00:00:00.000 1 99101 2003-11-15 00:00:00.000 1 99101 2003-11-29 00:00:00.000 0 99101 2003-12-06 00:00:00.000 0 99101 2003-12-13 00:00:00.000 0 99101 2003-12-20 00:00:00.000 0 99101 2003-12-29 00:00:00.000 0 99101 2004-01-03 00:00:00.000 1 99101 2004-01-10 00:00:00.000 0 99101 2004-01-17 00:00:00.000 0 99101 2004-01-24 00:00:00.000 0 99101 2004-01-31 00:00:00.000 0 99101 2004-02-28 00:00:00.000 0 99101 2004-03-03 00:00:00.000 0 99101 2004-03-06 00:00:00.000 0 99102 2002-11-24 00:00:00.000 1 99102 2003-11-15 00:00:00.000 1 99102 2003-11-29 00:00:00.000 0 99102 2003-12-06 00:00:00.000 0 99102 2003-12-13 00:00:00.000 0 99102 2003-12-20 00:00:00.000 0 99102 2003-12-29 00:00:00.000 0 99102 2004-01-03 00:00:00.000 1 99102 2004-01-10 00:00:00.000 0 99102 2004-01-17 00:00:00.000 0 99102 2004-01-24 00:00:00.000 0 99102 2004-01-31 00:00:00.000 0 99102 2004-02-28 00:00:00.000 0 99102 2004-03-03 00:00:00.000 0 99102 2004-03-06 00:00:00.000 0
rotaxe程序员 说的对,同意楼上.
select ID,AnDate, (case when exists(select 1 from 表 where ID = a.ID and year(AnDate) = year(a.AnDate) and AnDate<a.AnDate) then 0 else 1 end) as AnStatus from 表 a
(case when exists(select 1 from 表 where ID = a.ID and AnDate<a.AnDate) then 0 else 1 end) as AnStatus
from 表 a
from test t
from test t
create table test(id int,andate datetime)
insert into test values(99101, '2002-11-24 0:00')
insert into test values(99101 ,'2003-11-15 0:00')
insert into test values(99101, '2003-11-29 0:00')
insert into test values(99101 ,'2003-12-6 0:00')
insert into test values(99101, '2003-12-13 0:00')
insert into test values(99101 ,'2003-12-20 0:00')
insert into test values(99101, '2003-12-29 0:00')
insert into test values(99101 ,'2004-1-3 0:00')
insert into test values(99101, '2004-1-10 0:00')
insert into test values(99101 ,'2004-1-17 0:00')
insert into test values(99101, '2004-1-24 0:00')
insert into test values(99101 ,'2004-1-31 0:00')
insert into test values(99101, '2004-2-28 0:00')
insert into test values(99101 ,'2004-3-3 0:00')
insert into test values(99101 ,'2004-3-6 0:00')
insert into test values(99102, '2002-11-24 0:00')
insert into test values(99102 ,'2003-11-15 0:00')
insert into test values(99102, '2003-11-29 0:00')
insert into test values(99102 ,'2003-12-6 0:00')
insert into test values(99102, '2003-12-13 0:00')
insert into test values(99102 ,'2003-12-20 0:00')
insert into test values(99102, '2003-12-29 0:00')
insert into test values(99102 ,'2004-1-3 0:00')
insert into test values(99102, '2004-1-10 0:00')
insert into test values(99102 ,'2004-1-17 0:00')
insert into test values(99102, '2004-1-24 0:00')
insert into test values(99102 ,'2004-1-31 0:00')
insert into test values(99102, '2004-2-28 0:00')
insert into test values(99102 ,'2004-3-3 0:00')
insert into test values(99102 ,'2004-3-6 0:00')
go
select id,andate,(case when exists (select * from test t1 where id=t.id and year(t1.andate)=year(t.andate) having min(andate)=t.andate) then 1 else 0 end) as anstatus
from test t结果:
99101 2002-11-24 00:00:00.000 1
99101 2003-11-15 00:00:00.000 1
99101 2003-11-29 00:00:00.000 0
99101 2003-12-06 00:00:00.000 0
99101 2003-12-13 00:00:00.000 0
99101 2003-12-20 00:00:00.000 0
99101 2003-12-29 00:00:00.000 0
99101 2004-01-03 00:00:00.000 1
99101 2004-01-10 00:00:00.000 0
99101 2004-01-17 00:00:00.000 0
99101 2004-01-24 00:00:00.000 0
99101 2004-01-31 00:00:00.000 0
99101 2004-02-28 00:00:00.000 0
99101 2004-03-03 00:00:00.000 0
99101 2004-03-06 00:00:00.000 0
99102 2002-11-24 00:00:00.000 1
99102 2003-11-15 00:00:00.000 1
99102 2003-11-29 00:00:00.000 0
99102 2003-12-06 00:00:00.000 0
99102 2003-12-13 00:00:00.000 0
99102 2003-12-20 00:00:00.000 0
99102 2003-12-29 00:00:00.000 0
99102 2004-01-03 00:00:00.000 1
99102 2004-01-10 00:00:00.000 0
99102 2004-01-17 00:00:00.000 0
99102 2004-01-24 00:00:00.000 0
99102 2004-01-31 00:00:00.000 0
99102 2004-02-28 00:00:00.000 0
99102 2004-03-03 00:00:00.000 0
99102 2004-03-06 00:00:00.000 0
(case when exists(select 1 from 表 where ID = a.ID and
year(AnDate) = year(a.AnDate) and AnDate<a.AnDate) then 0 else 1 end) as AnStatus
from 表 a