比如现在要显示6月份按周统计的报表，如何按周显示，用的是mysql。哪位兄弟指点下~~

比如现在要显示6月份按周统计的报表，如何按周显示，用的是mysql。哪位兄弟指点下~~ 
06-01~06-07   06-08~06-14    06-15~06-21  06-22~06-28   06-29~06-30 
C1 C2 C3       C1 C2 C3       C1 C2 C3     C1 C2 C3      C1 C2 C3 
1   2  3       2   3  4        2  3  3     2   3  1      2   1  3     
4   4  3       1   1   1       1   1  1     1   1  1     2   2   2 

解决方案 »

  1.   

    建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
    一般是SUM(IF(
      

  2.   

    CREATE TABLE t_test(
        id  INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
        name VARCHAR(60) NOT NULL,
        create_date  DATE NOT NULL,
        PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;测试数据
    insert into t_test(name,create_date) values
    ("a","2009-06-01"),
    ("b","2009-06-02"),
    ("c","2009-06-03"),
    ("d","2009-06-04"),
    ("e","2009-06-05"),
    ("f","2009-06-06"),
    ("g","2009-06-07"),
    ("h","2009-06-08"),
    ("i","2009-06-09"),
    ("j","2009-06-10"),
    ("k","2009-06-11"),
    ("l","2009-06-12"),
    ("m","2009-06-13"),
    ("n","2009-06-14"),
    ("o","2009-06-15"),
    ("p","2009-06-16"),
    ("q","2009-06-17"),
    ("r","2009-06-18"),
    ("s","2009-06-19"),
    ("t","2009-06-20"),
    ("u","2009-06-21"),
    ("v","2009-06-22"),
    ("w","2009-06-23"),
    ("x","2009-06-24"),
    ("y","2009-06-25"),
    ("z","2009-06-26"),
    ("A","2009-06-27"),
    ("B","2009-06-28"),
    ("C","2009-06-29"),
    ("D","2009-06-30");
      

  3.   

    C1 C2 C3是什么？建议同时贴出基于这些数据的正确结果是什么？mysql> select * from t_test;
    +----+------+-------------+
    | id | name | create_date |
    +----+------+-------------+
    |  1 | a    | 2009-06-01  |
    |  2 | b    | 2009-06-02  |
    |  3 | c    | 2009-06-03  |
    |  4 | d    | 2009-06-04  |
    |  5 | e    | 2009-06-05  |
    |  6 | f    | 2009-06-06  |
    |  7 | g    | 2009-06-07  |
    |  8 | h    | 2009-06-08  |
    |  9 | i    | 2009-06-09  |
    | 10 | j    | 2009-06-10  |
    | 11 | k    | 2009-06-11  |
    | 12 | l    | 2009-06-12  |
    | 13 | m    | 2009-06-13  |
    | 14 | n    | 2009-06-14  |
    | 15 | o    | 2009-06-15  |
    | 16 | p    | 2009-06-16  |
    | 17 | q    | 2009-06-17  |
    | 18 | r    | 2009-06-18  |
    | 19 | s    | 2009-06-19  |
    | 20 | t    | 2009-06-20  |
    | 21 | u    | 2009-06-21  |
    | 22 | v    | 2009-06-22  |
    | 23 | w    | 2009-06-23  |
    | 24 | x    | 2009-06-24  |
    | 25 | y    | 2009-06-25  |
    | 26 | z    | 2009-06-26  |
    | 27 | A    | 2009-06-27  |
    | 28 | B    | 2009-06-28  |
    | 29 | C    | 2009-06-29  |
    | 30 | D    | 2009-06-30  |
    +----+------+-------------+
    30 rows in set (0.00 sec)mysql>
      

  4.   

    不好意思没说清楚，正确的结果应该是06-01~06-07  
    a~g
    06-08~06-14    
    h~n
    06-15~06-21  
    o~u
    06-22~06-28  
    v~B
    06-29~06-30
    C,D 
      

  5.   

    mysql> select concat(date_add('2009-06-01',interval (DAY(create_date)-1) div 7day) ,
        ->  '~',
        ->  date_add('2009-06-07',interval (DAY(create_date)-1) div 7  day)) as sWeek,
        ->  group_concat(name)
        -> from t_test
        -> group by (DAY(create_date)-1) div 7;
    +-----------------------+--------------------+
    | sWeek                 | group_concat(name) |
    +-----------------------+--------------------+
    | 2009-06-01~2009-06-07 | a,g,f,e,d,c,b      |
    | 2009-06-02~2009-06-08 | n,m,l,k,j,i,h      |
    | 2009-06-03~2009-06-09 | u,t,s,r,q,p,o      |
    | 2009-06-04~2009-06-10 | B,A,z,y,x,w,v      |
    | 2009-06-05~2009-06-11 | C,D                |
    +-----------------------+--------------------+
    5 rows in set (0.02 sec)mysql>
      

  6.   

    谢谢，那竖着显示记录呢，不用~
    06-01~06-07  
    a
    b
    c
    d
    e
    f
    g
      

  7.   

    2009-06-01~2009-06-07  2009-06-08~2009-06-14  2009-06-15~2009-06-21  2009-06-22~2009-06-27  2009-06-29~2009-06-30 
    1 a 2009-06-01 8 h 2009-06-08 15 o 2009-06-15 22 v 2009-06-22 29 C 2009-06-29
    2 b 2009-06-02 9 i 2009-06-09 16 p 2009-06-16 23 w 2009-06-23 30 D 2009-06-30
    3 c 2009-06-03 10 j 2009-06-10 17 q 2009-06-17 24 x 2009-06-24
    4 d 2009-06-04 11 k 2009-06-11 18 r 2009-06-18 25 y 2009-06-25
    5 e 2009-06-05 12 l 2009-06-12 19 s 2009-06-19 26 z 2009-06-26
    6 f 2009-06-06 13 m 2009-06-13 20 t 2009-06-20 27 A 2009-06-27
    7 g 2009-06-07 14 n 2009-06-14 21 u 2009-06-21 28 B 2009-06-28
      

  8.   

    2009-06-01~2009-06-07  2009-06-08~2009-06-14 2009-06-15~2009-06-21 2009-06-22~2009-06-27 2009-06-29~2009-06-30 
    1  a  2009-06-01 8  h 2009-06-08       15 o 2009-06-15     22 v  2009-06-22     29 C 2009-06-29
    2  b  2009-06-02 9  i 2009-06-09       16 p 2009-06-16     23 w  2009-06-23     30  D 2009-06-30
    3  c  2009-06-03 10 j 2009-06-10       17 q 2009-06-17     24 x  2009-06-24
    4  d  2009-06-04 11 k 2009-06-11       18 r 2009-06-18     25 y  2009-06-25
    5  e  2009-06-05 12 l 2009-06-12       19 s 2009-06-19     26 z  2009-06-26
    6  f  2009-06-06 13 m 2009-06-13       20 t 2009-06-20     27 A  2009-06-27
    7  g  2009-06-07 14 n 2009-06-14       21 u 2009-06-21     28 B  2009-06-28 这个是调整好的
      

  9.   

    2009-06-01~2009-06-07  2009-06-08~2009-06-14 2009-06-15~2009-06-21 2009-06-22~2009-06-27 2009-06-29~2009-06-30  
    1  a  2009-06-01 8           h 2009-06-08       15 o 2009-06-15     22 v  2009-06-22     29 C 2009-06-29 
    2  b  2009-06-02 9           i 2009-06-09       16 p 2009-06-16     23 w  2009-06-23     30  D 2009-06-30 
    3  c  2009-06-03 10          j 2009-06-10       17 q 2009-06-17     24 x  2009-06-24 
    4  d  2009-06-04 11          k 2009-06-11       18 r 2009-06-18     25 y  2009-06-25 
    5  e  2009-06-05 12          l 2009-06-12       19 s 2009-06-19     26 z  2009-06-26 
    6  f  2009-06-06 13          m 2009-06-13       20 t 2009-06-20     27 A  2009-06-27 
    7  g  2009-06-07 14          n 2009-06-14       21 u 2009-06-21     28 B  2009-06-28 
      

  10.   

    当您的问题得到解答后请及时结贴.
    http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
    mysql> select (DAY(create_date)-1) % 7 + 1,
        ->  min(if((DAY(create_date)-1) div 7=0,name,null)) as '2009-06-01~2009-06-07',
        ->  min(if((DAY(create_date)-1) div 7=0,create_date,null)) as tdate1,
        ->  min(if((DAY(create_date)-1) div 7=1,name,null)) as '2009-06-08~2009-06-14',
        ->  min(if((DAY(create_date)-1) div 7=1,create_date,null)) as tdate2,
        ->  min(if((DAY(create_date)-1) div 7=2,name,null)) as '2009-06-15~2009-06-21',
        ->  min(if((DAY(create_date)-1) div 7=2,create_date,null)) as tdate3,
        ->  min(if((DAY(create_date)-1) div 7=3,name,null)) as '2009-06-22~2009-06-27',
        ->  min(if((DAY(create_date)-1) div 7=3,create_date,null)) as tdate4,
        ->  min(if((DAY(create_date)-1) div 7=4,name,null)) as '2009-06-29~2009-06-30',
        ->  min(if((DAY(create_date)-1) div 7=4,create_date,null)) as tdate5
        -> from t_test
        -> group by (DAY(create_date)-1) % 7;
    +------------------------------+-----------------------+------------+-----------------------+------------+-----------------------+------------+-----------------------+------------+-----------------------+------------+
    | (DAY(create_date)-1) % 7 + 1 | 2009-06-01~2009-06-07 | tdate1     | 2009-06-08~2009-06-14 | tdate2     | 2009-06-15~2009-06-21 | tdate3     | 2009-06-22~2009-06-27 | tdate4     | 2009-06-29~2009-06-30 | tdate5     |
    +------------------------------+-----------------------+------------+-----------------------+------------+-----------------------+------------+-----------------------+------------+-----------------------+------------+
    |                            1 | a                     | 2009-06-01 | h                     | 2009-06-08 | o                     | 2009-06-15 | v                     | 2009-06-22 | C                     | 2009-06-29 |
    |                            2 | b                     | 2009-06-02 | i                     | 2009-06-09 | p                     | 2009-06-16 | w                     | 2009-06-23 | D                     | 2009-06-30 |
    |                            3 | c                     | 2009-06-03 | j                     | 2009-06-10 | q                     | 2009-06-17 | x                     | 2009-06-24 | NULL                  | NULL       |
    |                            4 | d                     | 2009-06-04 | k                     | 2009-06-11 | r                     | 2009-06-18 | y                     | 2009-06-25 | NULL                  | NULL       |
    |                            5 | e                     | 2009-06-05 | l                     | 2009-06-12 | s                     | 2009-06-19 | z                     | 2009-06-26 | NULL                  | NULL       |
    |                            6 | f                     | 2009-06-06 | m                     | 2009-06-13 | t                     | 2009-06-20 | A                     | 2009-06-27 | NULL                  | NULL       |
    |                            7 | g                     | 2009-06-07 | n                     | 2009-06-14 | u                     | 2009-06-21 | B                     | 2009-06-28 | NULL                  | NULL       |
    +------------------------------+-----------------------+------------+-----------------------+------------+-----------------------+------------+-----------------------+------------+-----------------------+------------+
    7 rows in set (0.00 sec)mysql>