sql语句分组查询
原始数据
姓名 时间 成交量
a 2011-01-01 18:30:00 100
a 2011-01-01 20:00:00 130
a 2011-01-02 19:00:00 150
a 2011-01-02 20:00:00 170
b 2011-01-01 19:00:00 120
b 2011-01-01 20:00:00 130
b 2011-01-02 19:00:00 150
b 2011-01-02 20:00:00 170
要求得到的结果：
姓名 时间 成交量
a 2011-01-01 18:30:00 100
a 2011-01-02 19:00:00 150
a 2011-01-02 20:00:00 170
b 2011-01-01 19:00:00 120
b 2011-01-02 19:00:00 150
即获取每人每天的成交量（有19点的数据就用19点的数据，没有就取距19点最近的1条记录）
数据库是sql server2005或者2008 sql 数据库 分组查询
[解决办法]
)select * from tb t1 where not exists(select 1 from tb t2 where t1.姓名=t2.姓名 and convert(varchar(10),t1.时间,120)=convert(varchar(10),t2.时间,120) and t1.时间>t2.时间)
create table Tbl_0321(name varchar(100), op_date datetime, num int)
insert Tbl_0321(name,op_date,num)
select
'a', '2011-01-01 18:30:00', 100 union all select
'a', '2011-01-01 20:00:00', 130 union all select
'a', '2011-01-02 19:00:00', 150 union all select
'a', '2011-01-02 20:00:00', 170 union all select
'b', '2011-01-01 19:00:00', 120 union all select
'b', '2011-01-01 20:00:00', 130 union all select
'b', '2011-01-02 19:00:00', 150 union all select
'b', '2011-01-02 20:00:00', 170
;with temp as(
select t.name
,CONVERT(varchar(10),t.op_date,120)+' 19:00:00' as op_date
,min(ABS(DATEDIFF(MINUTE,CONVERT(varchar(10),t.op_date,120)+' 19:00:00',t.op_date))) as min_date
from Tbl_0321 t
group by t.name,CONVERT(varchar(10),t.op_date,120)
)
select *
,(select num from Tbl_0321 tb
where temp.name=tb.name
and ABS(datediff(minute,temp.op_date,tb.op_date))=temp.min_date
)as num
,(select tb.op_date from Tbl_0321 tb
where temp.name=tb.name
and ABS(datediff(minute,temp.op_date,tb.op_date))=temp.min_date
)as original_date
from temp order by name
