MySQL：有没有办法有条件地左连接？（或类似？）

发布于 2024-08-10 10:33:49 字数 762 浏览 7 评论 0原文

我正在编写一个查询来获取 products 表中的所有产品，以及每个产品的销售价格如果存在记录specials 表中的该项目。

我正在寻找的是这样的：

SELECT * FROM products P
IF (S.specials_date_available <= NOW() AND S.expires_date > NOW())
{ // The sale has started, but has not yet expired
    LEFT JOIN specials S
      ON P.products_id = S.products_id
}

我意识到 MySQL 不是一种编程语言，但是有没有办法创建一个查询，从而产生与上述逻辑等效的查询？

结果集应如下所示：

 ID    Name         Price     Sale Price
 1     Widget A     10.00     (empty, because this item has no sale record)
 2     Widget B     20.00     15.45 (this item is currently on sale)
 3     Widget C     22.00     (empty - this item was on sale but the sale expired)

原文

I'm writing a query to get ALL of the products in the products table, and the sale price for each product IF a record exists for that item in the specials table.

What I'm looking for is something like:

SELECT * FROM products P
IF (S.specials_date_available <= NOW() AND S.expires_date > NOW())
{ // The sale has started, but has not yet expired
    LEFT JOIN specials S
      ON P.products_id = S.products_id
}

I realise MySQL is not a programming language, but is there a way to create a query that results in the logical equivalent of the above?

The result set should look like:

 ID    Name         Price     Sale Price
 1     Widget A     10.00     (empty, because this item has no sale record)
 2     Widget B     20.00     15.45 (this item is currently on sale)
 3     Widget C     22.00     (empty - this item was on sale but the sale expired)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情未る 2024-08-17 10:33:49

是的，您可以将条件移至查询的 JOIN ON 部分。

SELECT *
FROM products P
LEFT JOIN specials S
     ON P.products_id = S.products_id AND
        S.specials_date_available <= NOW() AND
        S.expires_date > NOW()

Yes, you can move the condition to the JOIN ON part of the query.

SELECT *
FROM products P
LEFT JOIN specials S
     ON P.products_id = S.products_id AND
        S.specials_date_available <= NOW() AND
        S.expires_date > NOW()

回复收藏 0 原文

情域 2024-08-17 10:33:49

SELECT * FROM products P
  LEFT JOIN specials S
    ON P.products_id = S.products_id AND S.specials_date_available <= NOW() AND S.expires_date > NOW()

SELECT * FROM products P
  LEFT JOIN specials S
    ON P.products_id = S.products_id AND S.specials_date_available <= NOW() AND S.expires_date > NOW()

回复收藏 0 原文

~没有更多了~