mysql中的右表条件的左连接 [英] left join with condition for right table in mysql

问题描述

我一直在努力解决这个问题，但进展不大.我的目标是在两个表之间进行左联接，并为右表建立条件.我希望看到当天所有产品和价格的列表，即使当天没有价格行.这是代码示例:

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.

好的，那只是我问题的第一部分.我最终还是希望在 CURRENT_DATE + INTERVAL 1 DAY 期间拉高价格.

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.

任何信息将不胜感激.

推荐答案

假定PRICES.date是DATETIME数据类型，请使用:

Assuming PRICES.date is a DATETIME data type, use:

   SELECT pd.id, 
          pd.name, 
          pr.price
     FROM PRODUCTS pd
LEFT JOIN PRICES pr ON pr.id = pd.id
                   AND DATE(pr.date) = CURRENT_DATE

我使用了 DATE函数删除时间部分，因为CURRENT_DATE在DATETIME记录中将不包括时间部分.

I used the DATE function to remove the time portion, because CURRENT_DATE won't include the time portion while DATETIME records will.

在此示例中，在创建JOIN之前 应用了date标准.这就像一个派生表，在进行JOIN之前会过滤掉信息-与在WHERE子句中指定条件相比，它将产生不同的结果.

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:

   SELECT pd.id, 
          pd.name, 
          pr.price
     FROM PRODUCTS pd
LEFT JOIN PRICES pr ON pr.id = pd.id
                   AND DATE(pr.date) = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY)

参考:

• DATE_ADD

如果您要在一个查询中同时查询今天和明天的价格，请使用:

If you want both todays and tomorrows prices in a single query, use:

   SELECT pd.id, 
          pd.name, 
          pr1.price AS price_today,
          pr2.price AS price_tomorrow
     FROM PRODUCTS pd
LEFT JOIN PRICES pr1 ON pr1.id = pd.id
                   AND DATE(pr1.date) = CURRENT_DATE
LEFT JOIN PRICES pr2 ON pr2.id = pd.id
                   AND DATE(pr2.date) = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY)

这篇关于mysql中的右表条件的左连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！