MySQL Left Join 未产生预期结果

Question

我知道左连接上有很多很好的问题得到解答，但我无法找到与我的特定问题相关的内容。对于可能重复的话题，我深表歉意，但我们将不胜感激。

我有两个包含礼品卡销售的表。我必须核实金额是否相符。 我正在寻求帮助编写一个查询，即使表 B 中没有匹配的记录，该查询也会返回表 A 中的所有内容。

表 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

这是到目前为止我的查询（显然有问题）

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，无论是否存在匹配。结果集是我期望使用内连接得到的结果。

Answer 1

只需将 B.business_date = '2011-10-06' 条件从 WHERE 移至 ON 子句即可。当您使用 LEFT 连接时，关于第二个表的列（IS (NOT) NULL 除外）的 WHERE 条件实际上会取消 >LEFT JOIN，然后它充当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

Answer 2

你应该使用这样的东西：

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' or B.business_date is null)
GROUP BY A.id
ORDER BY A.id ASC

Answer 3

我不确定您为什么使用“group by”子句 - 这里不需要它，并且在大多数其他数据库中，这将是一个语法错误。

我假设您只需要匹配 id 上的行。

这应该有效：

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
where A.business_date = '2011-10-06'  
  and A.am_pm = 'PM'
order by a.id

Answer 4

问题在于 B.business_date 的 where 条件，它也应该允许 null

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 A.id = B.id
WHERE A.am_pm = 'PM'
AND A.business_date = '2011-10-06'
AND (B.business_date = '2011-10-06' OR B.business_date is null )
ORDER BY A.id ASC