我想从表ProductItem中获取每条记录，并且对于每条记录，从表units_in_stock中获取字段ProductAttrsInfo的总和，并将主表连接到一些父子表。
我要检索的完整字段是：ProductItem.id AS product_item_idProductItem.name AS product_item_nameSUM(ProductAttrsInfo.units_in_stock) AS units_in_stockIFNULL(SUM(TransOrderItem.quantity), 0) AS total_ordered_items
当然，要获得total_ordered_items，我需要加入TransOrderItem，但是当我加入它时，总和结果是不正确的。
这是表关系图：
+-----------+
|ProductType|
+-----+-----+
| 1-M
v
+--------------+ +----------+
|ProductSubtype| |TransOrder|
+------+-------+ +-----+----+
| 1-M | 1-M
v v
+-----------+ +--------------+
|ProductItem| |TransOrderItem|
+-----+-----+ +-------+------+
| 1-M | 1-M
v |
+-----------+ |
|ProductAttr|<--------------+
+-----+-----+
| 1-1
v
+----------------+
|ProductAttrsInfo|
+----------------+
这是不加入表
TransOrderItem的查询：SELECT
ProductSubtype.id AS product_subtype_id,
ProductItem.id AS product_item_id,
ProductItem.name AS product_item_name,
SUM(ProductAttrsInfo.units_in_stock) AS units_in_stock
FROM
product_items AS ProductItem
LEFT JOIN product_attrs AS ProductAttr ON
ProductItem.id = ProductAttr.product_item_id
LEFT JOIN product_attrs_infos AS ProductAttrsInfo ON
ProductAttr.id = ProductAttrsInfo.product_attr_id
RIGHT JOIN product_subtypes AS ProductSubtype ON
ProductSubtype.id = ProductItem.product_subtype_id
RIGHT JOIN product_types AS ProductType ON
ProductType.id = ProductSubtype.product_type_id
GROUP BY
ProductItem.id
ORDER BY
ProductSubtype.id ASC,
ProductItem.id ASC
以上查询的结果，是正确的：
+------------------------------------------------------------------------------+
| product_item_id | product_item_name | units_in_stock |
+------------------------------------------------------------------------------+
| 1 | Kaos Pria O-Neck | 6935 |
| 2 | Kaos Pria Two-Tone | 1999 |
| 10 | Kaos Pria - Gildan 63000 Soft Style | 1163 |
| 30 | Kaos Pria | Gildan 64V00 - VNeck | 48 |
| 31 | Kaos Pria - Gildan 76400 - Lengan Panjang | 522 |
| 130 | Kaos Pria V-Neck | 574 |
| ... | ... | ... |
+------------------------------------------------------------------------------+
这是连接了
TransOrderItem表的查询：SELECT
ProductSubtype.id AS product_subtype_id,
ProductItem.id AS product_item_id,
ProductItem.name AS product_item_name,
SUM(ProductAttrsInfo.units_in_stock) AS units_in_stock,
IFNULL(SUM(TransOrderItem.quantity), 0) AS total_ordered_items
FROM
product_items AS ProductItem
INNER JOIN product_attrs AS ProductAttr ON
ProductItem.id = ProductAttr.product_item_id
INNER JOIN product_attrs_infos AS ProductAttrsInfo ON
ProductAttr.id = ProductAttrsInfo.product_attr_id
LEFT JOIN trans_order_items AS TransOrderItem ON
ProductAttr.id = TransOrderItem.product_attr_id AND
(TransOrderItem.created BETWEEN '2017-12-01' AND '2017-12-31') AND
EXISTS (
SELECT 1 FROM trans_orders AS TransOrder
WHERE
TransOrderItem.trans_order_id = TransOrder.id AND
TransOrder.status = 'TRS_SHP_FNS'
)
INNER JOIN product_subtypes AS ProductSubtype ON
ProductSubtype.id = ProductItem.product_subtype_id
INNER JOIN product_types AS ProductType ON
ProductType.id = ProductSubtype.product_type_id
GROUP BY
ProductItem.id
ORDER BY
ProductSubtype.id ASC,
ProductItem.id ASC
上面查询的结果具有错误的
units_in_stock总和： +-----------------+-------------------------------------------+----------------+
| product_item_id | product_item_name | units_in_stock |
+------------------------------------------------------------------------------+
| 1 | Kaos Pria O-Neck | 51285 |
| 2 | Kaos Pria Two-Tone | 6175 |
| 10 | Kaos Pria - Gildan 63000 Soft Style | 9532 |
| 30 | Kaos Pria | Gildan 64V00 - VNeck | 48 |
| 31 | Kaos Pria - Gildan 76400 - Lengan Panjang | 3047 |
| 130 | Kaos Pria V-Neck | 937 |
| ... | ... | ... |
+-----------------+-------------------------------------------+----------------+
我的查询中是否有任何错误的操作导致总和不正确？带有说明的正确查询将作为答案。谢谢。
-
编辑：添加了表结构：
`product_attrs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_item_id` int(10) unsigned NOT NULL DEFAULT '0',
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_product_attrs_product_items` (`product_item_id`),
CONSTRAINT `fk_product_attrs_product_items` FOREIGN KEY (`product_item_id`) REFERENCES `product_items` (`id`)
)
`product_attrs_infos` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_attr_id` int(10) unsigned NOT NULL DEFAULT '0',
`units_in_stock` int(10) NOT NULL DEFAULT '0',
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `product_attr_id` (`product_attr_id`),
CONSTRAINT `fk_product_attrs_info_product_attr_id` FOREIGN KEY (`product_attr_id`) REFERENCES `product_attrs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)
`product_items` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
`product_subtype_id` int(10) unsigned NOT NULL DEFAULT '0',
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `product_subtype_id` (`product_subtype_id`),
CONSTRAINT `product_items_ibfk_1` FOREIGN KEY (`product_subtype_id`) REFERENCES `product_subtypes` (`id`)
)
`trans_orders` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`status` enum('TRS_OPN','TRS_PAID','TRS_RFD','TRS_EXPR','TRS_INPROCESS','TRS_SHP_OPN','TRS_SHP_FNS','TRS_HOLD') DEFAULT 'TRS_OPN',
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
)
`trans_order_items` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`trans_order_id` int(10) unsigned NOT NULL,
`product_attr_id` int(10) unsigned NOT NULL DEFAULT '0',
`status` enum('TRS_OPN','PRNT_OPN','PRNT_FNS','QA_OPN','QA_PASS','QA_FAIL','PRNT_FAIL_OPN','PRNT_FAIL_FNS') NOT NULL DEFAULT 'TRS_OPN',
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_trans_order_items_trans_order_id` (`trans_order_id`),
KEY `fk_to_items_product_attr_id` (`product_attr_id`),
CONSTRAINT `fk_to_items_product_attr_id` FOREIGN KEY (`product_attr_id`) REFERENCES `product_attrs` (`id`),
CONSTRAINT `fk_trans_order_items_trans_order_id` FOREIGN KEY (`trans_order_id`) REFERENCES `trans_orders` (`id`)
)
最佳答案
您需要在单独的子条款中根据total_ordered_items计算数量，然后对主查询进行左连接，以避免此类问题
SELECT
st.id AS product_subtype_id,
i.id AS product_item_id,
i.name AS product_item_name,
SUM(ai.units_in_stock) AS units_in_stock,
IFNULL(oi.total_ordered_items, 0) AS total_ordered_items
FROM
product_items AS i
INNER JOIN product_attrs AS a ON
i.id = a.product_item_id
INNER JOIN product_attrs_infos AS ai ON
a.id = ai.product_attr_id
INNER JOIN product_subtypes AS st ON
st.id = i.product_subtype_id
INNER JOIN product_types AS t ON
t.id = st.product_type_id
LEFT JOIN (
SELECT toi.product_attr_id,
SUM(toi.quantity) total_ordered_items
FROM trans_order_items as toi
WHERE toi.created BETWEEN '2017-12-01' AND '2017-12-31'
AND EXISTS (
SELECT 1
FROM trans_orders AS o
WHERE toi.trans_order_id = o.id
AND o.status = 'TRS_SHP_FNS'
)
GROUP BY toi.product_attr_id
) as oi ON a.id = oi.product_attr_id
GROUP BY
st.id,
i.id,
i.name
ORDER BY
st.id ASC,
i.id ASC