在mysql怎样每7条记录汇总为一条记录,比例:数据里有
ID name
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
10 j
11 k
12 l
13 m
14 n
15 o
16 p
17 q汇总后为:
1 a,b,c,d,e,f,g
2 h,i,j,k,l,m,n
3 o,p,q
ID name
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
10 j
11 k
12 l
13 m
14 n
15 o
16 p
17 q汇总后为:
1 a,b,c,d,e,f,g
2 h,i,j,k,l,m,n
3 o,p,q
FROM tt GROUP BY CEILING(id / 7)
from 比例:数据里有
group by (id-1) div 7
2 b 2011-09-02 18:51:28
3 c 2011-09-03 18:51:28
5 e 2011-09-05 18:51:28
6 f 2011-09-06 18:51:28
7 g 2011-09-07 18:51:28
8 h 2011-09-08 18:51:28
9 i 2011-09-09 18:51:28
10 j 2011-09-10 18:51:28
11 k 2011-09-11 18:51:28
12 l 2011-09-12 18:51:28
13 m 2011-09-13 18:51:28
14 n 2011-09-14 18:51:28
15 o 2011-09-15 18:51:28
16 p 2011-09-16 18:51:28
17 q 2011-09-17 18:51:28
18 r 2011-09-18 18:51:28
19 s 2011-09-19 18:51:28
20 t 2011-09-20 18:51:28
21 u 2011-09-21 18:51:28
22 v 2011-09-22 18:51:28
23 x 2011-09-23 18:51:28
24 y 2011-09-24 18:51:28
25 z 2011-09-25 18:51:28
26 aa 2011-09-26 18:51:28
27 bb 2011-09-27 18:51:28
28 cc 2011-09-28 18:51:28
29 dd 2011-09-29 18:51:28
30 ee 2011-09-30 18:51:28
31 ff 2011-10-01 18:51:28
32 gg 2011-10-02 18:51:28
33 hh 2011-10-03 18:51:28
34 ii 2011-10-04 18:51:28
35 jj 2011-10-05 18:51:28
36 kk 2011-10-06 18:51:28
37 ll 2011-10-07 18:51:28
38 mm 2011-10-08 18:51:28
39 nn 2011-10-09 18:51:28 其实我最终目标是要以7天为一周来汇总!
FROM tt GROUP BY week(日期字段)
WEEK(date[,mode]) This function returns the week number for date. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used. See Section 5.2.3, “System Variables”. The following table describes how the mode argument works. First day
Mode of week Range Week 1 is the first week …
0 Sunday 0-53 with a Sunday in this year
1 Monday 0-53 with more than 3 days this year
2 Sunday 1-53 with a Sunday in this year
3 Monday 1-53 with more than 3 days this year
4 Sunday 0-53 with more than 3 days this year
5 Monday 0-53 with a Monday in this year
6 Sunday 1-53 with more than 3 days this year
7 Monday 1-53 with a Monday in this year mysql> SELECT WEEK('1998-02-20');
-> 7
mysql> SELECT WEEK('1998-02-20',0);
-> 7
mysql> SELECT WEEK('1998-02-20',1);
-> 8
mysql> SELECT WEEK('1998-12-31',1);
-> 53SELECT GROUP_CONCAT(name)
FROM tt GROUP BY week(日期字段)+1 自行修改数字
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。