mysql - 我如何知道选择左连接时 'hit' 范围内的哪个值？

Question

如果我有这个 (MySQL) 语句:

   SELECT h.*
     FROM houses h 
LEFT JOIN avail a ON (h.id = a.house_id
                 AND (    a.date_occupied = '2012-03-07'
                       OR a.date_occupied = '2012-03-08'
                       OR a.date_occupied = '2012-03-09'
                       OR a.date_occupied = '2012-03-10'))
    WHERE sleeps_count >= '1'
      AND active = 1
      AND a.house_id IS NULL
ORDER BY h.name

有没有办法获取结果中返回的匹配的“date_occupied”？还是我手动查看结果，然后找出实际命中的日期？

Answer 1

首先，它可能只是:

a.date_occupied IN ('2012-03-07', '2012-03-08', '2012-03-09', '2012-03-10')

或者:

a.date_occupied BETWEEN '2012-03-07' AND '2012-03-10'

要确定哪个日期是匹配，只需检查 a.date_occupied。由于根据 JOIN 条件已知它位于正确的范围内，因此您可以简单地将其包含在 SELECT 列表中。