php mysql 按周分组_php – 在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);

我有这个表的很多数据.如果我想显示结果,我该怎么做：

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()命令？

解决方法:

你需要做这样的事情

SELECT

n.name,

w1.amount,

w2.amount,

...

IFNULL(w1.amount,0) + IFNULL(w2.amount,0) + .... AS total

FROM

yourTable AS n

LEFT JOIN (

SELECT name, SUM(qty) AS amount FROM yourTable WHERE DAY(date) BETWEEN 1 AND 7 GROUP BY name

) AS w1 USING (name)

LEFT JOIN (

SELECT name, SUM(qty) AS amount FROM yourTable WHERE DAY(date) BETWEEN 8 AND 14 GROUP BY name

) AS w2 USING (name)

...

标签：counting,php,sql,mysql5

来源： https://codeday.me/bug/20191002/1841921.html