sql话语分组查询

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.时间&gt;t2.时间)

[解决办法]

引用:

当天有19点记录的时候就取19点数据，没有的时候才去获取最近的一条记录

这一步你试试用datediff获取差额，然后取min()的那条
[解决办法]


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

对SQL的时间处理不熟。。感觉绕了好几圈

sql话语分组查询

热点推荐