SQL - 使用逻辑运算符(放置括号的位置)

Question

我的查询应该计算上个月将特定客户添加到表中的次数。如果仅使用：

WHERE Return_Table.DateAdded >= DATEADD(month, -1, GETDATE())

然后我得到了上个月的所有"CustomerID"和"TimesReturn"，这似乎是有效的，但是当我使用"和"和"OR"搜索特定条目时 - 我得到如下所示的结果，但是此结果不再计算上个月的条目。

事实上，当我在搜索中包含或多或少的特定客户时，"TimesReturn"值会发生变化。因此，如果我搜索"cust1-4"，然后搜索"cust1-2" - TimesReturning计数为1和2更改。

我的查询：

SELECT TOP 20 Return_Table.Customer_ID, COUNT(*) AS TimesReturned
FROM Return_Table         
WHERE Return_Table.DateAdded >= DATEADD(month, -1, GETDATE()) AND
Return_Table.Customer_ID ='cust1' OR
Return_Table.Customer_ID ='cust2' OR
Return_Table.Customer_ID ='cust3' OR
Return_Table.Customer_ID ='cust4'
GROUP BY Return_Table.Customer_ID
ORDER BY TimesReturned DESC

结果：

Customer_ID TimesReturned
Cust1         24
Cust2         14
Cust3         4
Cust4         2
.....        ....

尽管此结果的格式正确，但我的查询一定有问题，才能像这样更改值。

Answer 1

仔细看看WHERE子句：

WHERE Return_Table.DateAdded >= DATEADD(month, -1, GETDATE()) AND
Return_Table.Customer_ID ='cust1' OR
Return_Table.Customer_ID ='cust2' OR
Return_Table.Customer_ID ='cust3' OR
Return_Table.Customer_ID ='cust4'

它将匹配以下行：

• 添加日期为上个月，客户 ID = 'cust1'
• 或客户 ID ='cust2'
• 或客户 ID = 'cust3'

等等。这就解释了为什么计数从原始(无客户 ID 筛选器(计数增加。添加括号，你应该很好：

WHERE
Return_Table.DateAdded >= DATEADD(month, -1, GETDATE()) AND
(
Return_Table.Customer_ID ='cust1' OR
Return_Table.Customer_ID ='cust2' OR
Return_Table.Customer_ID ='cust3' OR
Return_Table.Customer_ID ='cust4'
)

PS：以上内容与以下内容相同：

WHERE
Return_Table.DateAdded >= DATEADD(month, -1, GETDATE()) AND Return_Table.Customer_ID ='cust1' OR
Return_Table.DateAdded >= DATEADD(month, -1, GETDATE()) AND Return_Table.Customer_ID ='cust2' OR
Return_Table.DateAdded >= DATEADD(month, -1, GETDATE()) AND Return_Table.Customer_ID ='cust3' OR
Return_Table.DateAdded >= DATEADD(month, -1, GETDATE()) AND Return_Table.Customer_ID ='cust4'

Answer 2

在运算符中使用而不是多个 OR：

SELECT TOP 20 Return_Table.Customer_ID,
COUNT(*) AS TimesReturned
FROM Return_Table         
WHERE Return_Table.DateAdded >= DATEADD(month, -1, GETDATE()) AND
Return_Table.Customer_ID in ('cust1' ,'cust2' 'cust3','cust4')
GROUP BY Return_Table.Customer_ID
ORDER BY  TimesReturned DESC

Answer 3

你需要使用括号，当你使用多个or时and最好使用括号，否则根据运算符优先级，你的结果将纵

SELECT TOP 20 Return_Table.Customer_ID,
COUNT(*) AS TimesReturned
FROM Return_Table         
WHERE Return_Table.DateAdded >= DATEADD(month, -1, GETDATE()) AND
(Return_Table.Customer_ID ='cust1' OR
Return_Table.Customer_ID ='cust2' OR
Return_Table.Customer_ID ='cust3' OR
Return_Table.Customer_ID ='cust4'
)
GROUP BY Return_Table.Customer_ID
ORDER BY  TimesReturned DESC