目前我有两个表(数量和金额),表A(ID,AMOUNT,DATE),表B(ID,MONEY,DATE),数据如下,
表A
1 10 2006-4-23 9:10:11
1 15 2006-4-23 9:20:00
2 20 2006-4-23 10:15:10
2 15 2006-4-23 17:10:00表B
1 100.00 2006-4-23 9:10:11
1 150.00 2006-4-23 9:20:00
2 60.00 2006-4-23 10:15:10
2 45.00 2006-4-23 17:10:00我要统计a.id,sum(a.amount),sum(b.money),a.date,其中a.date所要得到的是某个时间段的起始时间(如2006-4-23 9:00到10:00这之间的时间都用2006-4-23 9:00:00来表示),而两个sum的值也是时间段的汇总之和,现在这里只是举例了一个时间段,现实是每天都有24个时间段,如何能够自动的得到所有时间段的汇总数据!请问高手如何实现??
表A
1 10 2006-4-23 9:10:11
1 15 2006-4-23 9:20:00
2 20 2006-4-23 10:15:10
2 15 2006-4-23 17:10:00表B
1 100.00 2006-4-23 9:10:11
1 150.00 2006-4-23 9:20:00
2 60.00 2006-4-23 10:15:10
2 45.00 2006-4-23 17:10:00我要统计a.id,sum(a.amount),sum(b.money),a.date,其中a.date所要得到的是某个时间段的起始时间(如2006-4-23 9:00到10:00这之间的时间都用2006-4-23 9:00:00来表示),而两个sum的值也是时间段的汇总之和,现在这里只是举例了一个时间段,现实是每天都有24个时间段,如何能够自动的得到所有时间段的汇总数据!请问高手如何实现??
convert(varchar(13),date,120) + ':00:00' as date
from
(select t1.id,t1.amount,t2.money,t1.date
from 表a t1,表b t2
where t1.id = t2.id and t1.date = t2.date) aa
group by id,convert(varchar(13),date,120)
insert @ta select 1,10,'2006-4-23 9:10:11'
union all select 1,15,' 2006-4-23 9:20:00'
union all select 2,20,'2006-4-23 10:15:10'
union all select 2,15,'2006-4-23 17:10:00'Declare @tb table(ID int,MONEY decimal(10,2),[DATE] datetime)
insert @tb select 1,100.00,'2006-4-23 9:10:11'
union all select 1,150.00,'2006-4-23 9:20:00'
union all select 2,60.00,'2006-4-23 10:15:10'
union all select 2,45.00,'2006-4-23 17:10:00'select * from @ta
select * from @tbselect a.id ,convert(char(13),a.[DATE],120) 时段,sum(a.amount) 总计,sum(b.money) 金额小计
from @ta a inner join @tb b on a.id=b.id
group by a.id,convert(char(13),a.[DATE],120)
order by a.id
--------------------------------------------------------------------------------------------
select a.id ,convert(char(13),a.[DATE],120) 时段,sum(a.amount) 总计,sum(b.money) 金额小计
from @ta a inner join @tb b on a.id=b.id
group by a.id,convert(char(13),a.[DATE],120)
order by a.id--------------------------------------------------------------------------------------------
得到的结果是:ID MONEY DATE
----------- ------------ ------------------------------------------------------
1 100.00 2006-04-23 09:10:11.000
1 150.00 2006-04-23 09:20:00.000
2 60.00 2006-04-23 10:15:10.000
2 45.00 2006-04-23 17:10:00.000(所影响的行数为 4 行)