34,575
社区成员
发帖
与我相关
我的任务
分享
f1 listdate remark
c 2011-12-12
a 2011-12-02 123
b 2011-11-01 44rr4
c 2011-12-01 774
c 2011-12-09 oiiie2
d 2011-12-2
a 2011-11-02 234
f1 listdate remark
a 2011-12-02 123
b 2011-11-01 44rr4
c 2011-12-09 oiiie2
if object_id('tb') is not null
drop table tb
go
create table tb
(
f1 varchar(10),
listdate varchar(10),
remark varchar(10)
)
go
insert into tb
select 'c','2011-12-12','' union all
select 'a','2011-12-02','123' union all
select 'b','2011-11-01','44rr4' union all
select 'c','2011-12-01','774' union all
select 'c','2011-12-09','oiiie2' union all
select 'd','2011-12-2','' union all
select 'a','2011-11-02','234'
go
select * from tb a where remark<>'' and not exists(select 1 from tb where f1=a.f1 and listdate>a.listdate and remark<>'')
go
/*
f1 listdate remark
---------- ---------- ----------
a 2011-12-02 123
b 2011-11-01 44rr4
c 2011-12-09 oiiie2
(3 行受影响)
*/
create table #t1
(f1 char(3),
listdate date,
remark varchar(9))
insert into #t1
select 'c', '2011-12-12', '' union all
select 'a', '2011-12-02', '123' union all
select 'b', '2011-11-01', '44rr4' union all
select 'c', '2011-12-01', '774' union all
select 'c', '2011-12-09', 'oiiie2' union all
select 'd', '2011-12-2', '' union all
select 'a', '2011-11-02', '234'
select a.*
from #t1 a
inner join
(select f1,max(listdate) maxlistdate
from #t1 where remark<>'' group by f1) b
on a.f1=b.f1 and a.listdate=b.maxlistdate
f1 listdate remark
---- ---------- ---------
a 2011-12-02 123
b 2011-11-01 44rr4
c 2011-12-09 oiiie2
(3 row(s) affected)
select
*
from
#t1 t
where
remark is not null
and
not exists(select 1 from #t1 where f1=t.f1 and remark is not null and listdate>t.listdate)
select *
from #t1 a
where remark is not null --如果不为空串,则 remark<>'' 下同
and not exists(select 1 from #t1 where f1=a.f1 and remark is not null and listdate>a.listdate)