我有一个order
与两个联接表关联的表:order_buyer
和order_seller
。
表结构如下所示:
CREATE TABLE IF NOT EXISTS `order` (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-- some fields...
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS order_seller (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
order_id INT(10) UNSIGNED NOT NULL,
seller_id INT(10) UNSIGNED NOT NULL,
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
CONSTRAINT fk_order_seller_order_id FOREIGN KEY (order_id) REFERENCES `order` (id) ON DELETE CASCADE,
CONSTRAINT fk_order_seller_seller_id FOREIGN KEY (seller_id) REFERENCES user (id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS order_buyer (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
order_id INT(10) UNSIGNED NOT NULL,
buyer_id INT(10) UNSIGNED NOT NULL,
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
CONSTRAINT fk_order_buyer_order_id FOREIGN KEY (order_id) REFERENCES `order` (id) ON DELETE CASCADE,
CONSTRAINT fk_order_buyer_buyer_id FOREIGN KEY (buyer_id) REFERENCES user (id) ON DELETE CASCADE
);
如果我是查看我的订单历史记录的买家,我想获取所有order_buyer.buyer_id
等于我的用户ID的订单。卖方也有同样的想法。
但是我的查询是这样的,我不知道是查询的是买主还是卖主。我只有一个用户ID。因此,我需要其中一个匹配,如果匹配,那么我想收集该订单。
以下查询不起作用,因为它给了我每笔订单:
SELECT `order`.*
FROM `order`
LEFT JOIN order_seller
ON order_seller.seller_id = 1
AND order_seller.order_id = order.id
LEFT JOIN order_buyer
ON order_buyer.buyer_id = 1
AND order_buyer.order_id = order.id
WHERE order.status = "PENDING"
下面的查询通过AND
从联接中删除and使其成为WHERE IN
结果集的子句来解决上述问题:
SELECT `order`.*
FROM `order`
LEFT JOIN order_seller
ON order_seller.seller_id = 1
LEFT JOIN order_buyer
ON order_buyer.buyer_id = 1
WHERE order.status = "PENDING"
AND order.id IN (order_buyer.order_id, order_seller.order_id)
我的问题是,是否有更好的查询来实现同一目标?这对我来说似乎是肮脏的。感觉不自然。
您可以将两个查询结合起来:
SELECT `order`.*
FROM `order`
LEFT JOIN order_seller
ON order_seller.seller_id = 1
AND order_seller.order_id = order.id
LEFT JOIN order_buyer
ON order_buyer.buyer_id = 1
AND order_buyer.order_id = order.id
WHERE order.status = "PENDING"
AND order.id IN (order_buyer.order_id, order_seller.order_id)
它应该比第二个查询快,但返回的结果相同。
您也可以将最后一个条件更改为
COALESCE(order_buyer.order_id, order_seller.order_id) IS NOT NULL
这基本上与Gordon的第一个查询相同。
但是-这里的问题是,引擎需要先读取所有待处理的订单,然后才能通过userID对其进行过滤。使用EXISTS子查询也会遇到同样的问题。
相反,我将使用UNION ALL查询:
SELECT `order`.*
FROM `order`
JOIN order_seller ON order_seller.order_id = order.id
WHERE order.status = "PENDING" AND order_seller.seller_id = 1
UNION ALL
SELECT `order`.*
FROM `order`
JOIN order_buyer ON order_buyer.order_id = order.id
WHERE order.status = "PENDING" AND order_buyer.buyer_id = 1
为了避免过多的代码重复,您可以使用UNION ALL子查询:
SELECT o.*
FROM (
SELECT order_id FROM order_seller WHERE seller_id = 1
UNION ALL
SELECT order_id FROM order_buyer WHERE buyer_id = 1
) x
JOIN `order` o ON o.id = x.order_id
WHERE o.status = "PENDING"
您将至少需要一个索引order(status)
。由于主键(order.id
)是它的隐式部分,因此索引可以同时用于JOIN-ON和WHERE子句。对于其他表,在order_seller(seller_id)
和上的索引order_buyer(buyer_id)
可能很好。但在复合索引order_seller(seller_id, order_id)
和order_buyer(buyer_id, order_id)
效果会更好。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句