在单个查询中，我需要按日期过滤的产品分别对采购和订单组中每个产品的数量求和。
例如，我需要知道到2017年和第12个月我购买了多少产品以及订购了多少产品。
比如：
产品|采购量|收到订单
玫瑰洗面奶| 50 | 22
皮包| 32 18
这是产品表，用于存储产品的详细信息。

CREATE TABLE t_product
(
    pkey INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    cname VARCHAR(99) NOT NULL,
);

这是采购表，用于存储采购的基本详细信息，如帐单id和日期。

CREATE TABLE t_purchase
(
    pkey INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    cdated DATETIME NOT NULL,
);

这是采购明细，其中存储了所采购的每个产品的详细信息

CREATE TABLE t_purchase_detail
(
    pkey INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    rpurchase INT(10) unsigned NOT NULL,
    rproduct INT(10) unsigned NOT NULL,
    crate DECIMAL(10,2) unsigned NOT NULL,
    cdiscount DECIMAL(5,2) unsigned NOT NULL,
    ctax DECIMAL(5,2) unsigned NOT NULL,
    cvolume INT(10) unsigned NOT NULL,
    CONSTRAINT t_purchase_detail_ibfk_2 FOREIGN KEY (rpurchase) REFERENCES t_purchase (pkey),
    CONSTRAINT t_purchase_detail_ibfk_3 FOREIGN KEY (rproduct) REFERENCES t_product (pkey)
);
CREATE INDEX rproduct ON t_purchase_detail (rproduct);
CREATE UNIQUE INDEX rpurchase ON t_purchase_detail (rpurchase, rproduct);

这是订单表，其中存储了订单的基本详细信息，如订单id和日期

CREATE TABLE t_order
(
    pkey INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    cdated DATETIME NOT NULL,
);

这是订单详细信息，其中存储了每个订购产品的详细信息

CREATE TABLE t_order_detail
(
    pkey INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    rproduct INT(10) unsigned NOT NULL,
    rorder INT(10) unsigned NOT NULL,
    csale DECIMAL(8,2) unsigned NOT NULL,
    cdiscount DECIMAL(5,2) unsigned DEFAULT '0.00' NOT NULL,
    ctax DECIMAL(5,2) unsigned DEFAULT '0.00' NOT NULL,
    cvolume SMALLINT(5) unsigned NOT NULL,
    CONSTRAINT t_order_detail_ibfk_1 FOREIGN KEY (rorder) REFERENCES t_order (pkey),
    CONSTRAINT t_order_detail_ibfk_2 FOREIGN KEY (rproduct) REFERENCES t_product (pkey)
);
CREATE UNIQUE INDEX roder ON t_order_detail (rorder, rproduct);
CREATE INDEX rproduct ON t_order_detail (rproduct);

pkey是所有表中的主键或id。
product表的pkey是purchase details和order details表中的外键。
purchase表的pkey是purchase details表中的外键
order表的pkey是order details表中的外键
编辑
这是我正在运行的查询：

SELECT
  t_product.cname                AS name,
  sum(t_purchase_detail.cvolume) AS volume,
  sum(t_order_detail.cvolume)    AS sold
FROM t_product, t_purchase, t_purchase_detail, t_order, t_order_detail
WHERE t_purchase.cgst = 1
      AND (t_product.pkey = t_purchase_detail.rproduct AND t_product.pkey = t_order_detail.rproduct)
      AND (t_purchase.pkey = t_purchase_detail.rpurchase AND t_order.pkey = t_order_detail.rorder)
      AND (
        (YEAR(t_purchase.cdated) <= 2017 AND MONTH(t_purchase.cdated) <= 12)
        AND
        (YEAR(t_order.cdated) <= 2017 AND MONTH(t_order.cdated) <= 12)
      )
GROUP BY t_product.pkey
ORDER BY name;

这是从采购明细表和订单明细表中选择常用产品。但有些情况下，有些产品只在采购明细表中可用，有些产品只在订单明细表中可用。如果一个产品在一个表中缺失，但在另一个表中存在，则该表中的产品体积应计算为0。例如，如果总共购买了30单位的橙色洗面奶，但没有订购，则产量应为
橙色洗面奶| 30 | 0
反之亦然。
编辑2
此查询返回所有产品，包括不在两个列表中的产品。

SELECT
  t_product.pkey            AS key_s,
  t_product.cname           AS NAME,

  (SELECT sum(t_purchase_detail.cvolume)
   FROM t_product, t_purchase, t_purchase_detail
   WHERE t_product.pkey = key_s
         AND t_product.pkey = t_purchase_detail.rproduct
         AND t_purchase.pkey = t_purchase_detail.rpurchase
         AND YEAR(t_purchase.cdated) <= 2017 AND MONTH(t_purchase.cdated) <= 12
   GROUP BY t_product.pkey) AS volume,

  (SELECT sum(t_order_detail.cvolume) AS sold
   FROM t_product, t_order, t_order_detail
   WHERE t_product.pkey = key_s
         AND t_product.pkey = t_order_detail.rproduct
         AND t_order.pkey = t_order_detail.rorder
         AND YEAR(t_order.cdated) <= 2017 AND MONTH(t_order.cdated) <= 12
   GROUP BY t_product.pkey) AS sold

FROM t_product
GROUP BY t_product.pkey
ORDER BY NAME;

我需要一种方法来写这个更有效地在一个干净的单一查询，可以删除产品不存在于任何的细节表。
谢谢您