MYSQL表日历与右Join

Question

如何填充行之间的空隙？我使用的是日历表，但我的查询似乎不正确。结果没有显示差距..

SELECT calendar.datefield, t2.*
    FROM store_relation t1,store t3, store_product t2
            RIGHT JOIN calendar ON (DATE(t2.created) = calendar.datefield)
            WHERE (calendar.datefield BETWEEN ('2012-07-15') and ('2012-07-20'))
    AND t1.id_store_a = 1
    AND t1.id_store_b = t2.id_store
    AND t3.id = t2.id_store
    AND t2.id_product = 11
    ORDER By t2.price

结果:

datefield   |   id_product   |   id_store   |   created
2012-07-15      1                1              2012-07-15
2012-07-18      1                1              2012-07-18
2012-07-20      1                1              2012-07-20
2012-07-20      1                1              2012-07-20

等待结果

datefield   |   id_product   |   id_store   |   created
2012-07-15      1                1              2012-07-15
2012-07-16      null             null           null
2012-07-17      null             null           null
2012-07-18      1                1              2012-07-18
2012-07-19      null             null           null
2012-07-20      1                1              2012-07-20
2012-07-20      1                1              2012-07-20

Answer 1

当您有一个 LEFT/RIGHT JOIN 并且您正在过滤可选地包含的表时，WHERE 子句会将这些记录排除在外。您需要包含一个允许遗漏 RIGHT JOIN 左侧部分的条件:

WHERE (calendar.datefield BETWEEN ('2012-07-15') and ('2012-07-20'))
AND
(
   (t1.id_store_a = 1
   AND t1.id_store_b = t2.id_store
   AND t3.id = t2.id_store
   AND t2.id_product = 11)
   OR
   (t2.id_product is NULL)
)

例如，如果您查看预期输出，id_product 的值为 null，但您过滤的是 t2.id_product = 11。所以 null 记录永远不会匹配 11，这就是它们被排除在外的原因。