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 manu.mid = :manu_id AND DATE(pord.orderDateTime) BETWEEN :orders_start AND :orders_end

我正在使用PHPDO,联系并核实脑膜炎(id)已经存在,订单_start/end被转换为MySQL的日表。

But the question I am trying to fidn out is, what is causing the problem, that when I add the date restriction, every product that was not ordered, is not displayed on the output?

编制表格

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

您需要的是将<代码> 序号标准移至加入条款,而不是将以下条款移至:

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 条款是“<代码>NUL的从外加入的数值”。如在<编码>产品_orders/code>上没有一行,则外在填入“UNL”的现场编号orderDatetime,并且该行将被过滤,因为NUL并不等于任何东西。

Answer 2

Try:

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

Edit: Corrected after ypercube comment.

Answer 3

在条款的哪里这样做。

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

在声明应当复述的条款的情况下,可以将第二行和职能视为另一条。仅此刻。让我知道这是否是trick。

Answer 4

我不知道您的具体系统如何运作,但可以打到<条形码>。您不妨尝试:

<编码> WHERE manu.mid = :manu_id and (DATE(pord.orderDatetime) BETWEEN:orders_start and :orders_end) OR pord.orderDatetime=NUL

如果情况并非如此,请举一个例子,说明<编码>日。你们想要的是什么时候?

友情链接