mysql—一个更好的查询，用于在两个表上进行左连接并只获得匹配的结果

whitzsjs 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(332)

我有一个 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 把它变成一个 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)

我的问题是，有没有更好的查询来实现同样的功能？我觉得这有点脏。感觉不自然。

sql mysql Database

来源：https://stackoverflow.com/questions/49837181/a-better-query-for-left-join-on-two-tables-and-getting-only-matching-results

3条答案

按热度按时间

kb5ga3dv1#

您可以通过添加 where 条款：

SELECT o.*
FROM `order` o LEFT JOIN
     order_seller os
     ON os.seller_id = 1 AND
        os.order_id = o.id LEFT JOIN
     order_buyer ob
     ON ob.buyer_id = 1 AND
        ob.order_id = o.id
WHERE o.status = 'PENDING' AND
      (os.order_id IS NOT NULL OR ob.order_id IS NOT NULL);

但是，我将使用 exists :

SELECT o.*
FROM `order` o
WHERE EXISTS (SELECT 1
              FROM order_seller os
              WHERE os.seller_id = 1 AND os.order_id = o.id
             ) OR
      EXISTS (SELECT 1
              FROM order_buyer ob
              WHERE ob.buyer_id = 1 AND ob.order_id = o.id
             )
WHERE o.status = 'PENDING' ;

为了提高性能，您需要在上的每个表上建立索引 (order_id, buyer_id) 以及 (order_id, seller_id) .

赞(0）回复(0）举报 2021-06-21

o0lyfsai2#

像这样的怎么样

select order.*
  from order
 where order.status = 'PENDING'
   and order.id in (
     select s.order_id from order_seller s where s.seller_id = 1
     union all
     select b.order_id from order_buyer b where b.buyer_id = 1)

或者使用现有的

select o.*
  from order o
 where o.status = 'PENDING'
   and (
     exists (select 1 
               from order_seller s 
              where s.seller_id = 1 and s.order_id = o.order_id)
     or
     exists (select 1 
               from order_buyer b 
              where b.buyer_id = 1 and b.order_id = o.order_id))

赞(0）回复(0）举报 2021-06-21

js81xvg63#

您可以将这两个查询组合起来：

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 )是它的隐式部分，索引可以同时用于连接on和where子句。其他表的索引 order_seller(seller_id) 以及 order_buyer(buyer_id) 也许可以。但综合指数 order_seller(seller_id, order_id) 以及 order_buyer(buyer_id, order_id) 会更好。

赞(0）回复(0）举报 2021-06-21

我来回答

mysql—一个更好的查询，用于在两个表上进行左连接并只获得匹配的结果

3条答案

相关问题

热门标签

最新问答