关于sql语句的写法解决方法

关于sql语句的写法
表#t1数据如下：

SQL code

f1        listdate        remarkc          2011-12-12       a          2011-12-02       123b          2011-11-01       44rr4c          2011-12-01       774c          2011-12-09       oiiie2d          2011-12-2        a          2011-11-02       234

我想取当listdate最大的日期，取remark的数据，并且remark不能为空

SQL code

f1        listdate        remarka          2011-12-02       123b          2011-11-01       44rr4c          2011-12-09       oiiie2

注意：不能取f1=c、listdate=2011-12-12的数据，因为remark为空的。
如何写sql语句？  


[解决办法]

SQL code

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)
[解决办法]
SQL code
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)
[解决办法]
探讨

SQL code
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)

[解决办法]
SQL code
create table #t1(f1 char(3),listdate date,remark varchar(9))insert into #t1select 'c', '2011-12-12', '' union all       select 'a', '2011-12-02', '123' union allselect 'b', '2011-11-01', '44rr4' union allselect 'c', '2011-12-01', '774' union allselect 'c', '2011-12-09', 'oiiie2' union allselect 'd', '2011-12-2', '' union all       select 'a', '2011-11-02', '234'select a.* from #t1 ainner join(select f1,max(listdate) maxlistdatefrom #t1 where remark<>'' group by f1) bon a.f1=b.f1 and a.listdate=b.maxlistdatef1   listdate   remark---- ---------- ---------a    2011-12-02 123b    2011-11-01 44rr4c    2011-12-09 oiiie2(3 row(s) affected)
[解决办法]
探讨

SQL code


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……

[解决办法]
select top 1 * from tb where remark is not null order by listdate desc
[解决办法]
select top 1 * from tb where remark is not null order by listdate desc

select t.* from tb t where remark is not null and listdate = (select max(listdate) from tb where f1 = t.f1 and remark is not null)

select t.* from tb t where remark is not null and not exists (select 1 from tb where f1 = t.f1 and remark is not null and listdate > t.listdate)

[解决办法]
SQL code
if object_id('tb') is not null   drop table tbgocreate table tb( f1 varchar(10), listdate varchar(10), remark varchar(10))goinsert into tbselect 'c','2011-12-12','' union allselect 'a','2011-12-02','123' union allselect 'b','2011-11-01','44rr4' union allselect 'c','2011-12-01','774' union allselect 'c','2011-12-09','oiiie2' union allselect 'd','2011-12-2','' union allselect 'a','2011-11-02','234'goselect * 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 123b          2011-11-01 44rr4c          2011-12-09 oiiie2(3 行受影响)*/ 
  

 

查看更多 下一篇