我们有一个产品,订单,order_items,order_status模式,如下所示。订单表状态字段,取值;已订购,已接收,已接收和已取消,如order_status表中所定义。
与该数据交互的UI确实具有“产品视图”,其中每个产品都有行。当用户选择产品时,下面的另一个视图(按订单显示)列出了所选产品所在的订单。
用户应该能够根据产品订单状态过滤产品视图。特别是,一个说“ On Order”的按钮应该过滤产品视图,以仅显示在In Orders视图中具有记录的产品记录,状态为“ ORDERED”。
下面的查询返回多个产品行,其源于一个产品存在于多个订单中。
SELECT products.*,
orders.`id` AS orderID,
orders.`status`
FROM products
LEFT JOIN order_items
ON products.`id` = order_items.`product_id`
JOIN orders
ON orders.`id` = order_items.`order_id`;
我们希望将上述结果设置为“订单状态”。也就是说,对于每个不同的订单状态,结果集应仅具有一个产品记录。然后,我们可以在“状态”字段中过滤产品视图。
下图显示了基于以上结果我们想要的结果集。红色意味着它应该不会是结果的一部分。
如上图所示;
如何实现呢?
用于GROUP BY
将多行折叠为一。用于MIN(o.id)
在每个组中获取定义明确的订单ID。
SELECT p.*, MIN(o.id) AS orderID, o.status
FROM products AS p
JOIN order_items AS oi ON oi.product_id = p.id
JOIN orders AS o ON o.id = oi.order_id
GROUP BY p.id, o.status
LEFT JOIN
在这种情况下使用没有意义。您永远不想按LEFT JOIN
表中的列分组,因为所有不匹配的行都将分组在一起。而且,如果您要过滤订单状态,则显然只需要订单中的产品。
我尝试了您的查询,但收到以下错误:'on子句'中的未知列'o.order_id'我显然是新手!
对不起,那显然应该是
o.id