100分在線求一SQL語句
現有一表如下 取的日期范圍06-12-15到07-01-14
日期 姓名 得分
2006-12-15 A 10
. A 20
. A
. A
2007-01-14 A
2006-12-15 B
. B
. B
2007-01-14 B
. C
. C
. C
. C
. D
. .
現求一語句或簡單的實現方法得到下面的結果將每周的得分匯總
姓名 開始日期(周開頭﹕日) 結束日期(周結尾﹕六) 一周得分和
A 2006-12-15 2006-12-16
A 2006-12-17 2006-12-23
..
B 2006-12-15 2006-12-16
..
..
謝謝
[解决办法]
select 姓名,cast(datepart(year,dateadd(day,-1,日期)) as varchar(4))+cast(datepart(week,dateadd(day,-1,日期)) as varchar(2)) as 年周,
sum(得分) as 得分 from tb
group by cast(datepart(year,dateadd(day,-1,日期)) as varchar(4))+cast(datepart(week,dateadd(day,-1,日期)) as varchar(2)),姓名
----
不好意思,写得太快了,再扩展一下就好了
[解决办法]
create table tb
(
t_date datetime,
t_week int
)
declare @s_date datetime,@e_date as datetime,@t_date as datetime,@i int,@j int
set @s_date = cast( '20061215 ' as datetime)
set @t_date = cast( '20061215 ' as datetime)
set @e_date = cast( '20070114 ' as datetime)
set @i = 1
set @j = 0
while (@t_date <=@e_date )
begin
SELECT @j=DATEPART(dw,@t_date)
if @j = 7
set @i = @i + 1
insert into tb values (@t_date,@i)
SELECT @t_date =dateadd(day,1,@t_date)
end
select 姓名,sum(分) from 表,tb where 日期 = t_date group by 姓名,t_week
[解决办法]
A2006-12-10~2006-12-1630.0
A2006-12-17~2006-12-2370.0
A2007-01-14~2007-01-2050.0
B2006-12-10~2006-12-16130.0
B2006-12-17~2006-12-2380.0
B2007-01-14~2007-01-2090.0
C2007-01-14~2007-01-20106.0
D2007-01-14~2007-01-204.0
[解决办法]
--拆分日期
create table T([Date] datetime)
insert T select '2006-12-15 '
union all select '2006-12-16 '
union all select '2006-12-17 '
union all select '2006-12-18 '
union all select '2006-12-19 '
union all select '2006-12-20 '
union all select '2006-12-21 '
union all select '2006-12-22 '
union all select '2006-12-23 '
union all select '2006-12-24 '
union all select '2006-12-25 '
union all select '2006-12-26 '
union all select '2006-12-27 '
union all select '2006-12-28 '
union all select '2006-12-29 '
union all select '2006-12-30 '
union all select '2006-12-31 '
union all select '2007-01-01 '
union all select '2007-01-02 '
union all select '2007-01-03 '
union all select '2007-01-04 '
union all select '2007-01-05 '
union all select '2007-01-06 '
union all select '2007-01-07 '
union all select '2007-01-08 '
union all select '2007-01-09 '
union all select '2007-01-10 '
union all select '2007-01-11 '
union all select '2007-01-12 '
union all select '2007-01-13 '
union all select '2007-01-14 '
select distinct
開始日期=case when (datepart(weekday, [Date])+@@datefirst-1)%7=0 then [Date]
when [Date]-6 between (select min([date]) from T) and (select max([date]) from T) then [Date]-6
else (select min([date]) from T) end,
結束日期=case when (datepart(weekday, [Date])+@@datefirst-1)%7=6 then [Date]
when [Date]+6 between (select min([date]) from T) and (select max([date]) from T) then [Date]+6
else (select max([date]) from T) end
from T
where (datepart(weekday, [Date])+@@datefirst-1)%7 in (0, 6)
--result
開始日期 結束日期
------------------------------------------------------ ------------------------------------------------------
2006-12-15 00:00:00.000 2006-12-16 00:00:00.000
2006-12-17 00:00:00.000 2006-12-23 00:00:00.000
2006-12-24 00:00:00.000 2006-12-30 00:00:00.000
2006-12-31 00:00:00.000 2007-01-06 00:00:00.000
2007-01-07 00:00:00.000 2007-01-13 00:00:00.000
2007-01-14 00:00:00.000 2007-01-14 00:00:00.000
(6 row(s) affected)
