关于sql语句的写法

fstao 2011-12-22 03:12:41
表#t1数据如下:

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



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

f1 listdate remark
a 2011-12-02 123
b 2011-11-01 44rr4
c 2011-12-09 oiiie2



注意:不能取f1=c、listdate=2011-12-12的数据,因为remark为空的。
如何写sql语句?
...全文
69 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
pengxuan 2011-12-22
  • 打赏
  • 举报
回复

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 行受影响)
*/
dawugui 2011-12-22
  • 打赏
  • 举报
回复
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)
dawugui 2011-12-22
  • 打赏
  • 举报
回复
select top 1 * from tb where remark is not null order by listdate desc
jmx123456789 2011-12-22
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 ap0405140 的回复:]
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……
[/Quote]
mark
唐诗三百首 2011-12-22
  • 打赏
  • 举报
回复

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)
勿勿 2011-12-22
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 qianjin036a 的回复:]
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)
[/Quote]+1
--小F-- 2011-12-22
  • 打赏
  • 举报
回复
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)
-晴天 2011-12-22
  • 打赏
  • 举报
回复
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)

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧