SQL:标记哪个WHERE条件匹配 [英] SQL: Mark which WHERE condition matched

问题描述

这是一个理论问题，我想知道是否存在一种很好的方法来找出WHERE语句中的哪个条件匹配.

This is a theoretical question, I was wondering if there is a good way of finding out which condition in the WHERE statements matched.

说我有一个这样的查询:

Say I have a query like this:

SELECT * FROM table WHERE
    COND1 OR
    (COND2 AND COND3) OR
    COND4

有什么办法知道哪个条件使给定的行匹配(或不匹配)?

Is there any way of knowing which of the conditions made a given row match (or unmatch)?

我想到的一个解决方案是在SELECT中添加一个CASE子句，然后为该行重新运行所有WHERE条件:

One solution i thought of was adding a CASE clause, to the SELECT, and rerunning all the WHERE conditions for that row:

SELECT *, which_cond = CASE .... END CASE ...

推荐答案

CASE是可能的.但是写得更短的是 IF() (至少使用MySQL):

CASE is a possibility. But shorter to write would be IF() (at least with MySQL):

SELECT *, IF(COND2, 1, 0) AS cond2, IF(COND3, 1, 0) as cond3 ... 

以及每个匹配的cond*(如果其值为1).

and every cond* matched if its value is 1.

当然，没有必要检查COND1是否匹配.对于所有得到的结果，COND1是正确的.因此IF(COND1, 1, 0)将始终返回1(在您的示例中).

Of course it does not make sense to check whether COND1 matches or not. For all results you get back, COND1 was true. So IF(COND1, 1, 0) will always return 1 (in your example).

更新:
至少对于MySQL，我再次发现，如果仅想获取1或0作为结果，则以下内容就足够了:

Update:
Again at least for MySQL, I discovered, that the following is sufficient if you only want to get either 1 or 0 as result:

SELECT *, COND2 AS cond2, COND3 as cond3 ...

并且关于Mark的答案，如果使用HAVING而不是WHERE(HAVING作为别名的访问权限)，则可以避免两次写条件:

and with respect to Mark's answer, you can avoid writing the conditions twice, if you use HAVING instead of WHERE (HAVING as access to aliases):

SELECT *, COND2 AS cond2, COND3 as cond3
FROM table
HAVING COND1 AND (cond2 OR cond3)

(注意:大写表示实际条件表达式，小写表示别名)

(note: uppercase means the actual condition expression and lowercase is the alias name)

更新2:

它的变化不大:您只需检查更新版本中通过OR连接的条件，就足以检查COND2是否为真.如果是这样，则COND3也是正确的，反之亦然.

Well it changes not that much: you only have to check conditions that are connected via OR in your updated version it would be sufficient to check whether COND2 is true or not. If so, then COND3 is also true and vice versa.

SELECT *, COND1 AS cond1, COND2 as cond2, COND4 as cond4
FROM table
HAVING cond1 OR (cond2 AND COND3) OR cond4

我认为这很清楚.

这篇关于SQL:标记哪个WHERE条件匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！