SQL时间判断求合问题，高手进
create table test(time datetime,money int)
insert into test select '2007-01-15 ',5
insert into test select '2007-01-16 ',5
insert into test select '2007-01-17 ',5
insert into test select '2007-01-18 ',5
insert into test select '2007-01-19 ',5
insert into test select '2007-01-20 ',5
insert into test select '2007-01-21 ',5
insert into test select '2007-01-22 ',5
insert into test select '2007-01-23 ',5
insert into test select '2007-01-24 ',5
insert into test select '2007-01-25 ',5
insert into test select '2007-01-26 ',5
insert into test select '2007-01-27 ',5
insert into test select '2007-01-28 ',5
insert into test select '2007-01-29 ',5
insert into test select '2007-01-31 ',5
insert into test select '2007-02-01 ',5
insert into test select '2007-02-02 ',5
insert into test select '2007-02-03 ',5
insert into test select '2007-02-04 ',5
insert into test select '2007-02-05 ',5
insert into test select '2007-02-06 ',5
insert into test select '2007-02-07 ',5
insert into test select '2007-02-08 ',5
insert into test select '2007-02-09 ',5
insert into test select '2007-02-10 ',5
insert into test select '2007-02-11 ',5
insert into test select '2007-02-12 ',5
insert into test select '2007-02-13 ',5
insert into test select '2007-02-14 ',5
insert into test select '2007-02-15 ',5
insert into test select '2007-02-16 ',5
insert into test select '2007-02-17 ',5
insert into test select '2007-02-18 ',5
insert into test select '2007-02-19 ',5
insert into test select '2007-02-20 ',5
insert into test select '2007-02-21 ',5
insert into test select '2007-02-22 ',5
insert into test select '2007-02-23 ',5
insert into test select '2007-02-24 ',5
insert into test select '2007-02-25 ',5
insert into test select '2007-02-26 ',5
insert into test select '2007-02-27 ',5
insert into test select '2007-02-28 ',5
insert into test select '2007-03-01 ',5
insert into test select '2007-03-02 ',5
insert into test select '2007-03-03 ',5
insert into test select '2007-03-04 ',5
insert into test select '2007-03-05 ',5
insert into test select '2007-03-06 ',5
insert into test select '2007-03-07 ',5
insert into test select '2007-03-08 ',5
问题：
当用户选择时间范围为2007-01-15到2007-02-02时
需要显示结果为
time money
2007年1月中旬 30
2007年1月下旬 50
2007年2月上旬 10
当用户选择时间范围为2007-01-22到2007-03-08时
需要显示结果为
time money
2007年1月下旬 45
2007年2月上旬 50
2007年2月中旬 50
2007年2月下旬 40
2007年3月上旬 40
也就是说时间的年，月，日都是由用户选择，我要动态生成的
这个问题有没有什么办法好解决啊？
[解决办法]
建議新增個欄位(description),用來定義該日期是屬於 [XXXX年xx月xx旬],就可以直接group by 了.
[解决办法]
create table test(time datetime,money int)
insert into test select '2007-01-15 ',5
insert into test select '2007-01-16 ',5
insert into test select '2007-01-17 ',5
insert into test select '2007-01-18 ',5
insert into test select '2007-01-19 ',5
insert into test select '2007-01-20 ',5
insert into test select '2007-01-21 ',5
insert into test select '2007-01-22 ',5
insert into test select '2007-01-23 ',5
insert into test select '2007-01-24 ',5
insert into test select '2007-01-25 ',5
insert into test select '2007-01-26 ',5
insert into test select '2007-01-27 ',5
insert into test select '2007-01-28 ',5
insert into test select '2007-01-29 ',5
insert into test select '2007-01-31 ',5
insert into test select '2007-02-01 ',5
insert into test select '2007-02-02 ',5
insert into test select '2007-02-03 ',5
insert into test select '2007-02-04 ',5
insert into test select '2007-02-05 ',5
insert into test select '2007-02-06 ',5
insert into test select '2007-02-07 ',5
insert into test select '2007-02-08 ',5
insert into test select '2007-02-09 ',5
insert into test select '2007-02-10 ',5
insert into test select '2007-02-11 ',5
insert into test select '2007-02-12 ',5
insert into test select '2007-02-13 ',5
insert into test select '2007-02-14 ',5
insert into test select '2007-02-15 ',5
insert into test select '2007-02-16 ',5
insert into test select '2007-02-17 ',5
insert into test select '2007-02-18 ',5
insert into test select '2007-02-19 ',5
insert into test select '2007-02-20 ',5
insert into test select '2007-02-21 ',5
insert into test select '2007-02-22 ',5
insert into test select '2007-02-23 ',5
insert into test select '2007-02-24 ',5
insert into test select '2007-02-25 ',5
insert into test select '2007-02-26 ',5
insert into test select '2007-02-27 ',5
insert into test select '2007-02-28 ',5
insert into test select '2007-03-01 ',5
insert into test select '2007-03-02 ',5
insert into test select '2007-03-03 ',5
insert into test select '2007-03-04 ',5
insert into test select '2007-03-05 ',5
insert into test select '2007-03-06 ',5
insert into test select '2007-03-07 ',5
insert into test select '2007-03-08 ',5
go
select
replace(convert(varchar(7),time,120), '- ', '年 ')+
case
when datepart(day,time) <11 then '上旬 '
when datepart(day,time) <21 then '中旬 '
else '下旬 '
end as time,
sum([money]) as [money]
from
test
where
time between '2007-01-22 ' and '2007-03-08 '
group by
replace(convert(varchar(7),time,120), '- ', '年 ')+
case
when datepart(day,time) <11 then '上旬 '
when datepart(day,time) <21 then '中旬 '
else '下旬 '
end
go
drop table test
go
[解决办法]
今天这么多高手在阿!
俺还是下潜吧
