mysql中右表的左连接条件
我一直在努力解决这个问题,但没有取得太大进展。我的目标是在两个表之间进行左连接,并使用右表的条件。我想查看当天所有产品和价格的列表,即使当天没有定价行。以下是代码示例:
SELECT products.id, products.name, prices.price
FROM products LEFT JOIN prices
ON products.id = prices.id
WHERE prices.date = CURRENT_DATE
这仅生成具有当前日期价格信息的产品。
好的,这只是我的问题的第一部分。我最终也想降低 CURRENT_DATE + INTERVAL 1 DAY 的价格。
任何信息将不胜感激。
I have been trying to wrap my head around this issue but I am not making much progress. My goal is to do a left join between two tables with criteria for the right table. I would like to see the list of all products and prices for the current day even though there is no pricing row for the current day. Here is an example of the code:
SELECT products.id, products.name, prices.price
FROM products LEFT JOIN prices
ON products.id = prices.id
WHERE prices.date = CURRENT_DATE
This produces only the products with price information for the current date.
OK, so that is just the first part of my issue. I would eventually like to pull the price for CURRENT_DATE + INTERVAL 1 DAY as well.
Any information would be greatly appreciated.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
假设
PRICES.date
是 DATETIME 数据类型,请使用:我使用了 DATE 函数 删除时间部分,因为
CURRENT_DATE
不会包含时间部分,而 DATETIME 记录会包含时间部分。在此示例中,在进行 JOIN 之前应用
日期
条件。它就像一个派生表,在进行 JOIN 之前过滤信息——这将产生与在 WHERE 子句中指定条件时不同的结果。要获取明天的产品和价格列表,请使用:
参考:
如果您希望在单个查询中同时显示今天和明天的价格,请使用:
Assuming
PRICES.date
is a DATETIME data type, use:I used the DATE function to remove the time portion, because
CURRENT_DATE
won't include the time portion while DATETIME records will.In this example, the
date
criteria is being applied before the JOIN is made. It's like a derived table, filtering down the information before the JOIN is made -- which'll produce different results than if the criteria was specified in the WHERE clause.To get the list of products and prices for tomorrow, use:
Reference:
If you want both todays and tomorrows prices in a single query, use:
上面的答案很有力。添加到 OMG Ponies 的回复...在原始 WHERE 语句中具有“正确”表条件基本上将 LEFT OUTER JOIN 转变为 INNER JOIN。只是以不同的方式看待它可能会有所帮助。
Strong answers above. Adding to OMG Ponies' reply... having 'right' table criteria in the original WHERE statement basically turns the LEFT OUTER JOIN into an INNER JOIN. Just a different way of looking at it that might help.