怎样对SQL中的datetime类型的值既可以对日期又可以对时间进行分组统计

[wei123456]

不好意思刚刚那个搞错了，应该是这样：
AmountTime(记录表)
Time ID Amount
2004-12-07 8:25:00 C001 2.0
2004-12-10 12:00:00 C002 5.0
2004-12-10 12:01:00 C002 3.0
2004-12-10 19:15:00 C001 1.0
2004-12-10 19:20:00 C002 5.0

TimeType(时间类型表)
TimeStart TimeEnd TimeType
7:00 10:00 A
11:30 13:30 B
18:00 20:00 C

想得到的结果如下：
(日期) (时间类型) (人数) (金额)
Date TimeType Number Amount
2004-12-07 A 1 2.0
2004-12-10 B 1 8.0
2004-12-10 C 2 6.0

[wei123456]

谢谢，谢谢，正在试....

[wei123456]

呵呵,你的答案让我无话可说

[wei123456]

唉,本来想等等,看看有没有其他写法的,结果等了一下午都没有,郁闷,不好意思,上面那个"dulei115() "让你也久等了!

[dulei115]

select cast(round(b.time) as datetime) as date, a.timetype,
count(distinct case when b.time - round(b.time, 0) between a.timestart and a.timeend then ID end) as number,
sum(case when b.time - round(b.time, 0) between a.timestart and a.timeend then b.amount end) as amount
from timetype a, amounttime b
group by round(b.time), a.timetype
order by date, timetype

[dulei115]

测试通过：
select *
from (select b.date, a.timetype,
count(distinct case when b.time between a.timestart and a.timeend then b.Id end) as number,
sum(case when b.time between a.timestart and a.timeend then b.amount end) as amount
from timetype a, (select cast(floor(cast(time as float)) as datetime) as date,
cast(cast(time as float) - floor(cast(time as float)) as datetime) as time, Id, amount
from amounttime) b
group by b.date, a.timetype) aa
where number is not null and amount is not null
order by date, timetype

[dulei115]

if object_id('AmountTime') is not null
drop table AmountTime
select cast('2004-12-07 8:25:00' as datetime) as Time, 'C001' as Id, 2.0 as Amount
into AmountTime
union
select '2004-12-10 12:00:00', 'C002', 5.0
union
select '2004-12-10 12:01:00', 'C002', 3.0
union
select '2004-12-10 19:15:00', 'C001', 1.0
union
select '2004-12-10 19:20:00', 'C002', 5.0

select * from AmountTime

if object_id('TimeType') is not null
drop table TimeType
select cast('7:00' as datetime) as TimeStart, cast('10:00' as datetime) as TimeEnd, 'A' as TimeType
into TimeType
union
select '11:30', '13:30', 'B'
union
select '18:00', '20:00', 'C'

select * from TimeType

select *
from (select b.date, a.timetype,
count(distinct case when b.time between a.timestart and a.timeend then b.Id end) as number,
sum(case when b.time between a.timestart and a.timeend then b.amount end) as amount
from timetype a, (select cast(floor(cast(time as float)) as datetime) as date,
cast(cast(time as float) - floor(cast(time as float)) as datetime) as time, Id, amount
from amounttime) b
group by b.date, a.timetype) aa
where number is not null and amount is not null
order by date, timetype

drop table AmountTime
drop table TimeType