请教分类查询近日记录

[kris2010]

ID Day Counts Offset BL ClassID
9 2008-6-27 ￥270.00 -1 Yes 4
10 2008-6-26 ￥550.00 0 Yes 8
11 2008-6-26 ￥20.00 0 Yes 8
12 2008-6-20 ￥250.00 0 Yes 4
14 2008-6-1 ￥100.00 0 Yes 4
15 2008-7-1 ￥50.00 0 Yes 8
以上表想查询距 7月1日 最近的记录, 按ClassID分类, 返回结果:
9 2008-6-27 ￥270.00 -1 Yes 4
15 2008-7-1 ￥50.00 0 Yes 8

请教该如何写这个记录.

[wwwwb]

select a.* from ttg1 a inner join
(SELECT ClassID,max(Day) as ma from ttg1 group by ClassID) b
on a.ClassID=b.ClassID and a.day=b.ma

[kris2010]

试用了下
ID Day Counts Offset BL ClassID
9 2008-6-27 ￥270.00 -1 Yes 4
10 2008-6-26 ￥550.00 0 Yes 8
11 2008-6-26 ￥20.00 0 Yes 8
12 2008-6-20 ￥250.00 0 Yes 4
14 2008-6-1 ￥100.00 0 Yes 4
15 2008-7-1 ￥50.00 0 Yes 8
16 2008-7-15 ￥100.00 0 Yes 8

这时返回
16 2008-7-15 ￥100.00 0 Yes 8
这并不是最接近7月1日的记录啊.

[wwwwb]

select b.[ClassID],b.day from (
select [ClassID],min(ce1) as aa from (SELECT [ClassID], (DATEDIFF('d',#7/1/2008#,[DAY])) AS ce,abs(ce) as ce1, *
FROM ttg1) group by [ClassID]) a
inner join
(SELECT [ClassID], (DATEDIFF('d',#7/1/2008#,[DAY])) AS ce,
abs(ce) as ce1, * FROM ttg1) b
on a.ClassID=b.ClassID and a.aa=b.ce1

[kris2010]

恩, 非常好用, 多谢!