表,PanDianid Name CheckDate
1 a 2007-2-1
2 a 2007-2-3
3 b 2007-2-4我现在要找出Name为a,时间最接近于现在时间的记录
用SELECT *
FROM PanDian
WHERE (Name = 'a') AND (CheckDate =
(SELECT MAX(CheckDate)
FROM PanDian))可以找出一条记录就是:2 a 2007-2-3
但是我想使用上面语句找出:3 b 2007-2-4的时候,也就是:
SELECT *
FROM PanDian
WHERE (Name = 'b') AND (CheckDate =
(SELECT MAX(CheckDate)
FROM PanDian))得到的却是一条也没有的空记录,如何解决?谢谢
1 a 2007-2-1
2 a 2007-2-3
3 b 2007-2-4我现在要找出Name为a,时间最接近于现在时间的记录
用SELECT *
FROM PanDian
WHERE (Name = 'a') AND (CheckDate =
(SELECT MAX(CheckDate)
FROM PanDian))可以找出一条记录就是:2 a 2007-2-3
但是我想使用上面语句找出:3 b 2007-2-4的时候,也就是:
SELECT *
FROM PanDian
WHERE (Name = 'b') AND (CheckDate =
(SELECT MAX(CheckDate)
FROM PanDian))得到的却是一条也没有的空记录,如何解决?谢谢
FROM PanDian
WHERE (Name = 'b') AND (CheckDate =
(SELECT MAX(CheckDate)
FROM PanDian WHERE (Name = 'b') ))
*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码) 最新版本:20070130http://www.cnblogs.com/feiyun0112/archive/2006/09/20/509783.html
SELECT *
FROM PanDian a
WHERE (Name = 'b') AND (CheckDate =
(SELECT MAX(CheckDate) FROM PanDian where name = a.name))--查找多条记录
select * from PanDian a
where not exists(select 1 from PanDian where name = a.name and CheckDate > a.CheckDate)
1 a 2007-2-1
2 a 2007-2-3
3 b 2007-2-4我现在要找出Name为a,时间最接近于现在时间的记录select name , max(checkdate) as checkdate from tb group by nameselect a.* from tb a,
(select max(checkdate) as checkdate from tb where name = 'a') b
where a.name = b.name and a.checkdate = b.checkdate
id int,
Name varchar(10),
CheckDate varchar(10)
)insert into T
select 1,'a','2007-2-1'
union all
select 2,'a','2007-2-3'
union all
select 3,'b','2007-2-4'
union all
select 4,'b','2007-2-6'
union all
select 5,'b','2007-2-2'
union all
select 6,'c','2006-12-31'select *
from T t1
where (checkdate=(select max(checkdate) from T where t1.name=T.name ))
order by name
----------------------------------
2 a 2007-2-3
4 b 2007-2-6
6 c 2006-12-31
where checkdate in(select max(checkdate) from test)