MySQL Left Join无法产生预期的结果 [英] MySQL Left Join not producing expected results

问题描述

我知道在 Left Joins 上有很多很好的答案，但是我很难找到与我的特定问题有关的东西.对于可能出现的重复主题，我深表歉意，但不胜感激.

I know there is a lot of good questions answered on Left Joins, but I am having trouble finding something pertaining to my particular problem. I apologize for a possible repeated topic, but help would be greatly appreciated.

我有两个包含礼品卡销售的表.我必须验证金额是否匹配. 我正在寻找写一个查询的帮助，即使表B中没有匹配的记录，该查询也将返回表A的所有内容.

I have two tables that contain gift card sales. I have to verify that the amounts match. I'm looking for help writing a query that will return everything from TABLE A even if there is no matching record in TABLE B.

表A

| id | business_date | am_pm | gift_cards_sold | 
================================================
 103 | 2011-10-06    | PM    | 175.03
 104 | 2011-10-06    | PM    | 135.03
 105 | 2011-10-06    | PM    | 250.74
 106 | 2011-10-06    | PM    | 180.44
 107 | 2011-10-06    | PM    | 150.10
 108 | 2011-10-06    | PM    | 130.00

表B

| id | business_date | am_pm | gift_cards_sold | 
================================================
 103 | 2011-10-06    | PM    | 100.03
 105 | 2011-10-06    | PM    | 250.74
 106 | 2011-10-06    | PM    | 180.44
 107 | 2011-10-06    | PM    | 150.10

到目前为止，这是我的查询(显然有问题)

Here is my query so far (obviously something wrong with it)

SELECT A.id AS ID, A.gift_cards_sold AS A_SOLD, B.gift_cards_sold AS B_SOLD
FROM A
LEFT JOIN B
USING (id)
WHERE A.am_pm = 'PM'
AND A.business_date = '2011-10-06'
AND B.business_date = '2011-10-06'
GROUP BY A.id
ORDER BY A.id ASC

这是结果:

| id | A_SOLD | B_SOLD | 
========================
 103 | 175.03 | 100.03
 105 | 250.74 | 250.74
 106 | 180.44 | 180.44
 107 | 150.10 | 150.10

您可以看到，id为103的变量.但是，无论是否存在匹配项，我都需要让结果显示每个id.结果集是我希望使用内部联接得到的结果.

As you can see, there is a variance with id of 103. However, I need to have the result show every id regardless if there is a match or not. The result set is what I would expect from using an Inner Join.

推荐答案

只需将B.business_date = '2011-10-06'条件从WHERE移到ON子句.当您具有LEFT联接时，关于第二个表的列的WHERE条件(除了IS (NOT) NULL的列)实际上取消了LEFT JOIN，然后将其用作INNER JOIN.

Simply move the B.business_date = '2011-10-06' condition from the WHERE to the ON clause. When you have a LEFT join, a WHERE condition about the second table's columns (except IS (NOT) NULL ones) actually cancels the LEFT JOIN, it then acts as an INNER JOIN.

SELECT A.id AS ID
     , A.gift_cards_sold AS A_SOLD
     , B.gift_cards_sold AS B_SOLD
FROM A
  LEFT JOIN B
    ON  B.id = A.id
    AND B.business_date = '2011-10-06'
WHERE A.am_pm = 'PM'
  AND A.business_date = '2011-10-06'
ORDER BY A.id

这篇关于MySQL Left Join无法产生预期的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！