求周数据

SQL code

tm                    s01  s02  s03  s04 ..... s302007-01-01 00:00:00   22   33   34   35  .....  212007-01-01 00:05:00   33   88   23   39  .....  532007-01-01 00:10:00   58   73   34   58  .....  78..........2008-01-01 00:00:00   22   33   34   35  .....  212008-01-01 00:05:00   33   88   23   39  .....  532008-01-01 00:10:00   58   73   34   58  .....  78............2009-01-01 00:00:00   22   33   34   35  .....  212009-01-01 00:05:00   33   88   23   39  .....  532009-01-01 00:10:00   58   73   34   58  .....  78...........2010-01-01 00:00:00   22   33   34   35  .....  212010-01-01 00:05:00   33   88   23   39  .....  532010-01-01 00:10:00   58   73   34   58  .....  78

我写了一条Sql求周最高值和平均值：

SQL code

SELECT    week(tm) as dayofweek,    year(tm) as dayofyear,    min(tm) as startdate,    max(tm) as enddate,    max(s09) as max_s09,    avg(s09) as avg_s09,    max(s08) as max_s08,    avg(s08) as avg_s08,    max(s07) as max_s07,    avg(s07) as avg_s07,    max(s06) as max_s06,    avg(s06) as avg_s06,    max(s05) as max_s05,    avg(s05) as avg_s05,    max(s04) as max_s04,    avg(s04) as avg_s04,    max(s03) as max_s03,    avg(s03) as avg_s03,    max(s02) as max_s02,    avg(s02) as avg_s02,    max(s01) as max_s01,    avg(s01) as avg_s01,    max(s10) as max_s10,    avg(s10) as avg_s10,    max(s11) as max_s11,    avg(s11) as avg_s11,    max(s19) as max_s19,    avg(s19) as avg_s19,    max(s18) as max_s18,    avg(s18) as avg_s18,    max(s17) as max_s17,    avg(s17) as avg_s17,    max(s16) as max_s16,    avg(s16) as avg_s16,    max(s15) as max_s15,    avg(s15) as avg_s15,    max(s14) as max_s14,    avg(s14) as avg_s14,    max(s13) as max_s13,    avg(s13) as avg_s13,    max(s12) as max_s12,    avg(s12) as avg_s12,    max(s20) as max_s20,    avg(s20) as avg_s20,    max(s21) as max_s21,    avg(s21) as avg_s21,    max(s29) as max_s29,    avg(s29) as avg_s29,    max(s28) as max_s28,    avg(s28) as avg_s28,    max(s27) as max_s27,    avg(s27) as avg_s27,    max(s26) as max_s26,    avg(s26) as avg_s26,    max(s25) as max_s25,    avg(s25) as avg_s25,    max(s24) as max_s24,    avg(s24) as avg_s24,    max(s23) as max_s23,    avg(s23) as avg_s23,    max(s22) as max_s22,    avg(s22) as avg_s22,    max(s30) as max_s30,    avg(s30) as avg_s30FROM    to_genesisGROUP BY    week(from_unixtime(tm)),    year(from_unixtime(tm))

中间自己做一个处理得到的结果为：

SQL code

dayofweek   dayofyear    startdate     enddate    pingjun     zuigao    subject0           2007         2007-1-1      2007-1-6   123         233       s091           2007         2007-1-7      2007-1-13  153         372       s09..........52          2007         2007-12-30    2007-12-31 233         388       s090           2008         2008-1-1      2008-1-5   123         382       s091           2008         2008-1-6      2008-1-12  233         283       s09.......

我现在的问题是，中间都没问题，但是头和尾的周都是残缺的，
比如头上
2007年的第0周，变成startdate:2006-12-31,enddat:2007-1-6
2007年的第52周，变成startdate:2007-1-7,enddate:2008-1-5

这样来统计么？

谁能帮我改下Sql啊。。谢谢啦~~

[解决办法]
生成所有星期的辅助表，与工作表连接再做处理
[解决办法]
2007-1-7,enddate:2008-1-5

算哪一周呢？ 2007年的第52周 还是 2008 第0周？