gpt4 book ai didi

mysql - 更好地查询两个表上的 LEFT JOIN 并仅获得匹配的结果

转载 作者:搜寻专家 更新时间:2023-10-30 21:56:01 26 4
gpt4 key购买 nike

我有一个 order 表,它与两个连接表相关联:order_buyerorder_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 并使其成为结果集的 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 的第一个查询基本相同。

但是 - 这里的问题是引擎需要先读取所有挂单,然后才能按用户 ID 过滤它们。 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) 的复合索引会更好。

关于mysql - 更好地查询两个表上的 LEFT JOIN 并仅获得匹配的结果,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/49837181/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com