SQL查询的where子句

5

我在我的SQL查询中有这一行:

WHERE client = $id
    AND (isinvoiced = 0) OR (isinvoiced = 1 and isrecurring = 1)

比我预期的要多的结果。然而,如果我这样写:

WHERE client = $id
    AND (isinvoiced = 0) OR (isinvoiced = 1 and isrecurring = 1 and client = $id)

然后它给我想要的结果,但这是编写代码的最佳方式吗?我只是不想再遇到任何问题。


1
请搜索“分配重写规则”(Distributivity rewrite rules)以获取更多信息。 - onedaywhen
2
你确定你理解a AND b OR ca AND (b OR c)之间的区别吗?你的例子更清晰地写成(a AND b) OR c,而不是你真正想要的内容。 - MatBailie
6个回答

8
你需要在整个AND子句周围再加一组()。这表明client = $id 必须为真,并且其他任何条件也必须为真,包括isinvoiced = 0isinvoiced = 1 and isrecurring = 1的组合。
 WHERE client = $id
    AND ((isinvoiced = 0) OR (isinvoiced = 1 and isrecurring = 1))

完美的Michael,这正是我正在寻找的,再次感谢...当它让我投票时,我会投票支持你的答案。 - user979331
1
如果 isinvoiced = 1 正好是 isinvoiced = 0 的相反情况(即 isinvoiced 不可能持有其他值,也不是 NULL),那么你甚至可以进一步简化它:WHERE client = $id AND (isinvoiced = 0 OR isrecurring = 1) - Andriy M

2
在你的AND子句周围添加括号:
WHERE client = $id
    AND ((isinvoiced = 0) OR (isinvoiced = 1 and isrecurring = 1))

1
where client = $id
    and (
        isinvoiced = 0
        or (
            isinvoiced = 1
            and isrecurring = 1
            )
        )

1
你想要的是这个:
WHERE client = $id AND ((isinvoiced = 0) OR (isinvoiced = 1 and isrecurring = 1))

如果您不加上额外的括号,它将与客户端限制进行 OR 操作并返回更多结果。


0
关于 SQL,您应该着眼于以 合取范式("一系列 AND 子句")编写搜索条件。有各种 重写规则 可协助完成此任务。
在这种情况下,分配律重写法非常有用。
( P AND Q ) OR R   <=>   ( P OR R ) AND ( Q OR R )    

针对您的情况:

( isinvoiced = 0 ) OR ( isinvoiced = 1 AND isrecurring = 1 )

可以重写为:

( isinvoiced = 0 OR isinvoiced = 1 ) AND ( isinvoiced = 0 OR isrecurring = 1 )

因此,整个搜索条件不需要繁琐的括号:

....
WHERE client = $id
      AND ( isinvoiced = 0 OR isinvoiced = 1 ) 
      AND ( isinvoiced = 0 OR isrecurring = 1 );

-1

如果您移除了最初的where子句

remove -> WHERE client = $id

仅仅拥有

 WHERE (isinvoiced = 0) OR (isinvoiced = 1 and isrecurring = 1 and client = $id)

这样能得到你想要的结果吗?


OP希望始终匹配“client”。请参见已接受的答案。 - Ben

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接