如何在左连接中使用不同的 WHERE 子句

Question

我有两个文本字段，我将每个文本字段值发布到 MySQL 查询中，特别是在 WHERE 子句中。

$sWhere = " WHERE MONTH(Inspection_datetime) = '".$Month."' AND 
            YEAR(Inspection_datetime) = '".$Year."' ";

然后我尝试将这个查询组合起来，如下所示：

$sQuery = "SELECT A.id,A.Line,
                  week1.1st, week2.2nd, week3.3rd, week4.4th, week5.5th,
                  IFNULL(week1.1st,0) + IFNULL(week2.2nd,0) + IFNULL(week3.3rd,0)
                + IFNULL(week4.4th,0) + IFNULL(week5.5th,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 1st
                     FROM inspection_report
                     WHERE DAY(Inspection_datetime) BETWEEN 1 AND 7
                     GROUP BY Line, WEEK(Inspection_datetime),
                                    YEAR(Inspection_datetime)
                     ) AS week1 USING (Line)

                 LEFT JOIN(
                           SELECT Line, (SUM(S) + SUM(A) + SUM(B)*0.4 + SUM(C)*0.1)/COUNT(Serial_number) AS 2nd
                           FROM inspection_report
                           WHERE DAY(Inspection_datetime) BETWEEN 8 AND 14
                           GROUP BY Line, WEEK(Inspection_datetime), YEAR(Inspection_datetime)
                           ) AS week2 USING (Line)";
.........//SAME LIKE ABOVE JUST CHANGE INTO another week's range

$sWhere = " WHERE MONTH(Inspection_datetime) = '".$Month."' AND
           YEAR(Inspection_datetime) = '".$Year."' ";

$sGroupBy = " GROUP BY A.Line ";
$sQuery.= $sWhere.$sGroupBy;

我希望它可以每周计算数据，并且还可以按 $Month 和 $Year 进行过滤。

但我得到了不正确的结果。结果每周统计整个月份和全年的数据。 我该如何修复它？

Answer 1

您需要将 WHERE MONTH(Inspection_datetime) = '".$Month."' AND YEAR(Inspection_datetime) = '".$Year. 添加到每个子查询

当前存在一些问题查询：

1. 您仅通过 Line 字段连接表，您告诉 MySql 要做的就是找到包含一个月和一年数据的行，并为这些行提取所有每周数据您
2. 要多次连接和分组表（每周一次），如果您可以按行获取每周结果，那么查询会更快吗
3. 您正在为每 7 创建一个新的子查询。 一个月中的几天，但希望按周显示结果