分组问题，要按条件进行分组

[kdg2000]

请教一个分组问题

T1

name date text
1 2007-3-1 t1
1 2007-3-2 t2
1 2007-3-8 t3
2 2007-3-1 t4
2 2007-3-2 t5
2 2007-3-9 t6


要以name进行分组，但是date既不是取最大也不是取最小，而是和某一个日期比较，如 2007-3-7 ，要取date比2007-3-7小，且最接近的一条记录

得到如下：
name date text
1 2007-3-2 t2
2 2007-3-2 t4

[子陌红尘]

declare @date datetime
set @date='2007-03-07'

select
t.*
from
表 t
where
t.date=(select max(date) from 表 where name=a.name and date<@date)

[子陌红尘]

declare @t table(name int,date datetime,[text] varchar(4))
insert into @t select 1,'2007-3-1','t1'
insert into @t select 1,'2007-3-2','t2'
insert into @t select 1,'2007-3-8','t3'
insert into @t select 2,'2007-3-1','t4'
insert into @t select 2,'2007-3-2','t5'
insert into @t select 2,'2007-3-9','t6'

declare @date datetime
set @date='2007-03-07'

select
t.*
from
@t t
where
t.date=(select max(date) from @t where name=t.name and date<@date)
order by
t.name

/*
name date text
----------- ------------------------------------------------------ ----
1 2007-03-02 00:00:00.000 t2
2 2007-03-02 00:00:00.000 t5
*/

[mengmou]

--创建测试环境
create table T1(name varchar(10),date datetime,[text] varchar(20))

--插入测试数据
insert T1(name,date,[text])
select '1','2007-3-1','t1' union all
select '1','2007-3-2','t2' union all
select '1','2007-3-8','t3' union all
select '2','2007-3-1','t4' union all
select '2','2007-3-2','t5' union all
select '2','2007-3-9','t6'

--求解过程
select * from T1 t
where date in (select top 1 date from T1 where date < '2007-3-7' order by date desc)

--删除测试环境
drop table T1

/*--测试结果
name date text
---------- ----------------------------- --------------------
1 2007-03-02 00:00:00.000 t2
2 2007-03-02 00:00:00.000 t5

（所影响的行数为 2 行）
*/

[kdg2000]

to libin_ftsafe(子陌红尘：当libin告别ftsafe)
我先run了一下你的方法，当出现下面的情况时，最终结果达不到分组的目的了
如下：

declare @t table(name int,date datetime,[text] varchar(4))
insert into @t select 1,'2007-3-1','t1'
insert into @t select 1,'2007-3-2','t2'
insert into @t select 1,'2007-3-8','t3'
insert into @t select 2,'2007-3-1','t4'
insert into @t select 2,'2007-3-2','t5'
insert into @t select 2,'2007-3-9','t6'
insert into @t select 1,'2007-3-8','t7' --这里新增加一条记录

declare @date datetime
set @date='2007-03-07'

select
t.*
from
@t t
where
t.date=(select max(date) from @t where name=t.name and date<@date)
order by
t.name

/*
name date text
----------- ------------------------------------------------------ ----
1 2007-03-02 00:00:00.000 t2
1 2007-03-02 00:00:00.000 t7
2 2007-03-02 00:00:00.000 t5
*/

[kdg2000]

错了，新增的那条记录应该是这个
insert into @t select 1,'2007-3-2','t7' --这里新增加一条记录

也就是说，相同name的情况下，它的date值也一样时候 ，

[starsky2006]

我来改一下libin_ftsafe(子陌红尘：当libin告别ftsafe) 的
select t.name,max(t.date),max(t.text)
from @t t
where t.date=(select max(date) from @t where name=t.name and date<@date)
group by t.name
order by t.name