特定数据查询

Question

链接到以前的线程链接的Oracle - runtime and show data based on inserted year ( not duplicate )

摘要

基于上面的链接，我继续编写我想要的更具体的结果，在使用"WHERE“语句显示特定数据的代码过程中遇到了一些问题。

基于Mr.Barboros提供的工作代码，我想添加一些查询，只显示某个位置，例如，

我有查询2016，它应该只显示2个结果

FACILITYNAME COUNT_TIMES    MONTHS
Science Lab     1             4
Science Lab     1             5

但它总是显示出额外的位置，即生物学，即使在2016年，数据库中也没有插入生物。-如果我用"OR“添加了更多的位置，它仍然会继续显示结果，下面是我的查询代码。

SELECT f.FACILITYNAME,COUNT(*) AS count_times, EXTRACT (MONTH FROM b.Timebooked ) AS MONTHS
FROM BookingTable b
JOIN FacilityTable f ON b.FACILITYNO = f.FACILITYNO
WHERE TO_CHAR(b.Timebooked , 'YYYY') = 2018
AND f.FACILITYNAME = 'Toilet' OR f.FACILITYNAME = 'Science Lab' OR  f.FACILITYNAME = 'Biology Lab'
GROUP BY (EXTRACT (MONTH FROM b.Timebooked )),f.FACILITYNAME
ORDER BY (EXTRACT (MONTH FROM b.Timebooked ));

http://sqlfiddle.com/#!4/13a6a/9

我想要的结果是，当我进入任何一年，只展示厕所，科学实验室生物实验室

Answer 1

需要在'OR‘条件组周围添加'()’。没有括号，AND将优先于OR。请参阅SQL Logic Operator Precedence。

试试这个：

SELECT f.FACILITYNAME,COUNT(*) AS count_times, EXTRACT (MONTH FROM b.Timebooked ) AS MONTHS
FROM BookingTable b
JOIN FacilityTable f ON b.FACILITYNO = f.FACILITYNO
WHERE TO_CHAR(b.Timebooked , 'YYYY') = 2018
AND (f.FACILITYNAME = 'Toilet' OR f.FACILITYNAME = 'Science Lab' OR  f.FACILITYNAME = 'Biology Lab')
GROUP BY (EXTRACT (MONTH FROM b.Timebooked )),f.FACILITYNAME
ORDER BY (EXTRACT (MONTH FROM b.Timebooked ));

如果没有括号，您的查询实际上就是这样做的(这是不正确的)：

SELECT f.FACILITYNAME,COUNT(*) AS count_times, EXTRACT (MONTH FROM b.Timebooked ) AS MONTHS
FROM BookingTable b
JOIN FacilityTable f ON b.FACILITYNO = f.FACILITYNO
WHERE ( TO_CHAR(b.Timebooked , 'YYYY') = 2018
AND  f.FACILITYNAME = 'Toilet') OR f.FACILITYNAME = 'Science Lab' OR  f.FACILITYNAME = 'Biology Lab' 
GROUP BY (EXTRACT (MONTH FROM b.Timebooked )),f.FACILITYNAME
ORDER BY (EXTRACT (MONTH FROM b.Timebooked ));