php - 连接表并用指定值替换空值

Question

我正在尝试制作一个可打印的页面，其中有指定制造商的所有销售额，列出了指定日期之间的所有产品。如果没有任何销售，则应显示 0。

表格

// Manufacturer table
// mid, manufacturer
// Products table
// pid, product, ref_manufacturer_id
// Orders table
// oid, orderPrice, orderDateTime, ref_product_id

有效的查询(没有日期限制)

SELECT prod.product, COALESCE(COUNT(pord.oid),0) AS orderCount,
COALESCE(SUM(pord.orderPrice),0) AS orderSum
FROM product_manufacturer AS manu
JOIN product_list AS prod ON prod.ref_manufacturer_id = manu.mid
LEFT JOIN product_orders AS pord ON pord.ref_product_id = prod.pid
WHERE manu.mid = :manu_id
GROUP BY prod.product;

但是一旦我将这个添加到 WHERE 语法中

WHERE manu.mid = :manu_id AND DATE(pord.orderDateTime) BETWEEN :orders_start AND :orders_end

我正在使用 PHP PDO 连接并验证 manu_id 是 int 并且 orders_start/end 已转换为 MySQL 日期格式。

但我想弄清楚的问题是，是什么导致了这个问题，当我添加日期限制时，每个未订购的产品都没有显示在输出中？

创建表的SQL

CREATE TABLE product_list (
  pid bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  product varchar(255) NOT NULL,
  ref_manufacturer_id bigint(20) unsigned NOT NULL,
  PRIMARY KEY (pid),
  KEY ref_manufacturer_id (ref_manufacturer_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE product_manufacturer (
  mid bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  manufacturer varchar(255) NOT NULL,
  PRIMARY KEY (mid),
  UNIQUE KEY manufacturer (manufacturer)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE product_orders (
  oid bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  orderPrice float(10,2) NOT NULL,
  orderDatetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  red_product_id bigint(20) unsigned NOT NULL,
  PRIMARY KEY (oid),
  KEY red_product_id (red_product_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Answer 1

您需要将 orderDateTime 条件移动到 join 子句而不是 where 子句，例如:

SELECT prod.product, COALESCE(COUNT(pord.oid),0) AS orderCount,
       COALESCE(SUM(pord.orderPrice),0) AS orderSum
FROM product_manufacturer AS manu
JOIN product_list AS prod ON prod.ref_manufacturer_id = manu.mid
LEFT JOIN product_orders AS pord 
          ON pord.ref_product_id = prod.pid
          AND DATE(pord.orderDateTime) BETWEEN :orders_start AND :orders_end
WHERE manu.mid = :manu_id
GROUP BY prod.product;

它在 WHERE 子句中不起作用的原因是因为从外部连接返回的 NULL 值。当您在产品的 product_orders 中没有行时，外部联接会为日期字段 orderDateTime 返回 NULL，并且该行将被过滤掉，因为 NULL 不等于任何东西。