mysql - 如何查找开始日期和结束日期之间的所有列值？异常输出

Question

我运行查询

SELECT * FROM roomcalendar where day between '2016-08-25' and '2016-08-28'

结果如下图所示。

但我想找到'2016-08-25'和'2016-08-28'之间所有avaroom的值1

如果我这样使用

SELECT * FROM roomcalendar where day between '2016-08-25' and '2016-08-28' and NOT avaroom=0

显示 1 行

我需要检查开始日期和结束日期之间的所有avaroom = 0。

怎么做？

Answer 1

问题是你想要什么结果？

SELECT 'YES' as Column_Name
FROM roomcalendar
WHERE day between '2016-08-25' and '2016-08-28'
  AND avaroom <> 0

这将返回 YES如果有任何带有 avaroom <> 0 的记录，如果没有则为 NULL。

如果您想选择全部，但仅当不存在 avaroom <> 0 时，则:

SELECT * FROM roomcalendar t
WHERE NOT EXISTS(SELECT 1 FROM roomcalendar s
                 WHERE s.day between '2016-08-25' and '2016-08-28'
                   AND s.avaroom <> 0)
 AND t.day between '2016-08-25' and '2016-08-28'