mysql 以周为单位记录数据_在MySQL中计数数据并按周分组

this is my DB table:

CREATE TABLE IF NOT EXISTS `inspection_report` (

`Inspection_datetime` datetime NOT NULL,

`Line` char(5) NOT NULL,

`S` int(11) NOT NULL,

`A` int(11) NOT NULL,

`B` int(11) NOT NULL,

`C` int(11) NOT NULL,

INSERT INTO `inspection_report` (`Inspection_datetime`,`Line`,`S`, `A`, `B`, `C`) VALUES

('2010-09-01 09:08:01','FA 05',0, 0, 0, 0),('2010-09-02 14:24:35','FA 07',0, 0, 1, 0),('2010-09-01 09:08:01','fa 05',0, 1, 1, 0),('2010-09-01 16:24:04','FA 03', 0, 1, 0, 0);

I have a lot of data for this table.how do i do if i want show the result like:

Line 1st week 2nd week 3rd week 4th week 5th week total

FA 03 20 32 10 12 35 109

FA 05 12 5 10 10 25 62

FA 07 0 0 1 1 0 2

there are a lot of data for a month. i want separate them counting for a week.if there is data that has reached about a week, then the script will automatically count them and share them in the 1st week,2nd week,3rd week,and so on. how do i do that? or are you have any idea? How about using YEARweek() command?

解决方案SELECT

A.Line,

week1.1stweek,

week2.2ndweek,

...

IFNULL(week1.1stweek,0) + IFNULL(week2.2ndweek,0) + .... AS total

FROM

inspection_report AS A

LEFT JOIN (

SELECT Line, (SUM(S) + SUM(A) + SUM(B)*0.4 + SUM(C)*0.1)/COUNT(Serial_number) AS 1stweek FROM inspection_report WHERE DAY(Inspection_datetime) BETWEEN 1 AND 7 GROUP BY Line, WEEK, YEAR) AS week1 USING (Line)

LEFT JOIN (

SELECT Line, (SUM(S) + SUM(A) + SUM(B)*0.4 + SUM(C)*0.1)/COUNT(Serial_number) AS 2ndweek FROM inspection_report WHERE DAY(Inspection_datetime) BETWEEN 8 AND 14 GROUP BY Line, WEEK, YEAR) AS week2 USING (Line)

...

GROUP BY Line