mysql - 我如何制作条件 WHERE 子句(或替代方式)

Question

我尝试计算所有处于“无效”状态的行。这可以是不同的状态、字段内容，而且，这是我的问题，当一个状态可以并且两个字段不相同时，它仍然会被视为无效

SELECT count(status) 
    FROM db.table 
    WHERE name LIKE  '".$needle."' AND 
    (
        (
            (                           
            status LIKE 'NOT%OK' OR         /* listing all status that we want to catch     */
            status LIKE 'XY' OR
            status LIKE 'XYZ' OR 
            unauth_change IS NOT NULL       /* also those where unauth_change is not empty  */
            ) OR 
            (
            status LIKE 'OK' AND            /* if the status is "ok" though and the     */
            planned_place = actual_place    /* planned and actual place on are not same     */
            )                               /* then still count it                          */
        )
        AND
        override NOT LIKE 'Y'               /* overridden items are discarded anyway        */      
    )  LIMIT 1;

我尝试了 IF 函数但失败了，我什至不确定它是否会按照我的预期工作。下面的查询是最新的测试，我想说“(‘状态’无效”或“状态有效，但计划和实际不一样”)和“其余条款”。我也尝试过 IF(status ='ok',planned_place =actual_place,1) 但老实说，我是在黑暗中钓鱼。

示例行和预期结果

name| status |  unauth_change | planned_place | actual_place | override | COUNT?|
---------------------------------------------------------------------------------------
abc | notok  |                | A             | A            | N        | yes
cde | xy     |                | A             | A            | N        | yes
efg | xyz    |                | A             | A            | Y        | no (override)
hij | ok     | blablabla      | A             | A            | N        | yes (unauth)
lmn | ok     |                | A             | B            | N        | yes (planned/actual)
opq | ok     |                | A             | A            | N        | no

列表项

Answer 1

尝试这样的事情:

SELECT COUNT(name)
FROM tableNameHere
WHERE name LIKE searchTermHere
AND (status NOT LIKE 'ok'
OR unauth_change IS NOT NULL
OR planned_place != actual_place)
AND override LIKE 'N'