SQL 列出具有实际所有者的所有公寓

Question

我正在尝试使用查询列出拥有实际所有者的所有公寓。

我的公寓 table 是这样的:

building | apartmentNbr | owner |      start |        end
      b1 |           a1 |    o1 | 2009-08-13 | 2010-08-13
      b1 |           a1 |    o2 | 2010-08-14 | 2019-01-01
      b1 |           a2 |    o3 | 2009-01-03 | 2010-01-03
      b1 |           a2 |    o4 | 2010-01-04 | 2010-08-13
      b1 |           a2 |    o5 | 2010-08-14 | 9999-12-31
      b1 |           a3 |    o6 | 2010-08-14 | 2019-12-31
      b1 |           a3 |    o7 | 2020-02-14 | 9999-12-31
      b2 |           b1 |    o8 | 2012-08-14 | 9999-12-31
...

根据我的查询

SELECT *
FROM Appartments
WHERE CURDATE() BETWEEN startDate AND endDate

我只有已占用的公寓，但我也想拥有这样的免费公寓:

building | apartmentNbr | owner
      b1 |           a1 |  null
      b1 |           a2 |    o5
      b1 |           a3 |  null
      b2 |           b1 |    o8

我该如何解决这个问题？

Answer 1

执行GROUP BY。使用 case 表达式获取当前所有者(或 null)。

SELECT building, apartmentNbr,
       max(case when CURDATE() BETWEEN startDate AND endDate then owner end)
FROM Appartments
GROUP BY building, apartmentNbr