tb1
Name Date
张三 2010/5/6
李四 2010/7/7
李四 2010/7/8
张三 2010/5/9
张三 2010/5/10查询结果
Flag Name Date
0 李四 2010/7/7
1 李四 2010/7/8
0 张三 2010/5/6
0 张三 2010/5/9
1 张三 2010/5/10
Name Date
张三 2010/5/6
李四 2010/7/7
李四 2010/7/8
张三 2010/5/9
张三 2010/5/10查询结果
Flag Name Date
0 李四 2010/7/7
1 李四 2010/7/8
0 张三 2010/5/6
0 张三 2010/5/9
1 张三 2010/5/10
where not exists(select 1
from tb
where t.name=name
and t.date<date)
(
name varchar(10),
date datetime
)
insert into tb values('张三','2010/5/6')
insert into tb values('李四','2010/7/7')
insert into tb values('李四','2010/7/8')
insert into tb values('张三','2010/5/9')
insert into tb values('张三','2010/5/10')select * from tb t where
not exists (select 1 from tb where name = t.name and date > t.date)
/*
name,date
李四,2010-07-08 00:00:00.000
张三,2010-05-10 00:00:00.000(2 行受影响)
还是这样?
0 李四 2010/7/7
1 李四 2010/7/8
0 张三 2010/5/6
0 张三 2010/5/9
1 张三 2010/5/10
(
name varchar(10),
date datetime
)
insert into tb values('张三','2010/5/6')
insert into tb values('李四','2010/7/7')
insert into tb values('李四','2010/7/8')
insert into tb values('张三','2010/5/9')
insert into tb values('张三','2010/5/10')select '0' as flag, * from tb t where
exists (select 1 from tb where name = t.name and date > t.date)
union
select '1' as flag, * from tb t where
not exists (select 1 from tb where name = t.name and date > t.date)
order by name, date
/*
flag,name,date
0,李四,2010-07-07 00:00:00.000
1,李四,2010-07-08 00:00:00.000
0,张三,2010-05-06 00:00:00.000
0,张三,2010-05-09 00:00:00.000
1,张三,2010-05-10 00:00:00.000(5 行受影响)
select [Flag]=(case when [No] = (select max([No]) from (select row_number() over (partition by [Name] order by [Name],[Date]) as [No], * from tb1) as tb where [Name] = a.[Name]) then 1 else 0 end)
,[Name]
,[Date]
from (select row_number() over (partition by [Name] order by [Name],[Date]) as [No], * from tb1) as a
(
name nvarchar(10),
dt datetime
)
insert into @tb values(N'张三','2010/5/6')
insert into @tb values(N'李四','2010/7/7')
insert into @tb values(N'李四','2010/7/8')
insert into @tb values(N'张三','2010/5/9')
insert into @tb values(N'张三','2010/5/10')select name,max(dt) from @TB group by name
declare @tb table(name varchar(10),date1 datetime)
insert into @tb values('张三','2010/5/6')
insert into @tb values('李四','2010/7/7')
insert into @tb values('李四','2010/7/8')
insert into @tb values('张三','2010/5/9')
insert into @tb values('张三','2010/5/10')
select case when date1=(select MAX(date1) from @tb where name=a.name) then 1 else 0 end as flag,* from @tb as a order by a.name
-----------------------------------------------------------
flag name date1
----------- ---------- -----------------------
0 李四 2010-07-07 00:00:00.000
1 李四 2010-07-08 00:00:00.000
0 张三 2010-05-09 00:00:00.000
1 张三 2010-05-10 00:00:00.000
0 张三 2010-05-06 00:00:00.000
select
case when not exists(select 1 from tb1 where t.name=name
and t.date<date) then 1 else 0 end as Flag,
t.[name],t,[date]
from tb1 t
select case when a.rowNum=1 then 1 else 0 end Flag,a.Name,a.Date
(select Name,Date,ROW_NUMBER() OVER(partition by Name order by Date desc) AS rowNum from tb) a
(select Name,Date,ROW_NUMBER() OVER(partition by Name order by Date desc) AS rowNum from tb) a
这个的效率高一点 可以在name和date1加索引