在MySQL中计数数据并按周分组 [英] counting data and grouping by week in mysql

问题描述

这是我的数据库表:

    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

一个月有很多数据.我想将它们分开计数一个星期.如果有数据已达到大约一周，则脚本将自动对它们进行计数并在第1周，第2周，第3周等中共享它们.我怎么做?还是有什么主意?如何使用YEARweek()命令?

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

这篇关于在MySQL中计数数据并按周分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！