在MYSQL查询中查找哪个条件失败（偏差汇总）

Question

如果任何 mysql 查询条件失败，我需要显示偏差摘要。我需要显示哪些数据输入错误，因为我得到了 NONE 结果。更多的逻辑问题所以试图用谷歌搜索同样的问题。表字段：id、day、date、time、program、title询问SELECT Count(id) FROM `table` WHERE day='Monday' and date = '2018-10-15' and time = '19:45:34' and title = 'NEW-BOTTLE'如果满足所有条件，上述查询将返回计数。如果任何条件值不满意，我需要显示偏差摘要。例如，如果 NEW-BOTTLE 输入为 NEW-BOTTLE1，那么我需要显示标题名称不正确。以同样的方式，我需要显示由于发生了哪个计数 0 而哪个值是错误的。

Answer 1

您可以使用if条件选择值，如下所示：

SELECT IF(day = 'Monday', 'TRUE','FALSE') as dayResult, IF(date = '2018-10-15', 'TRUE','FALSE') as dateResult,

IF(time = '19:45:34', 'TRUE','FALSE') as timeResult, IF(title = 'NEW-BOTTLE', 'TRUE','FALSE') as titleResult   FROM `table` 

它将返回要么1或0根据条件满足。希望它以任何方式帮助你。

Answer 2

您可以使用 switch 语句打印详细摘要。这是伪代码。

switch (true) {

case 0:

    SELECT Count(id) FROM `table`

    WHERE day = 'Monday' and date = '2018-10-15' and time = '19:45:34' and title = 'NEW-BOTTLE'

    // Get the count from query    

    if ($count != '0') {

        // Criteria met successfully, not need to check other cases;

        break;

    }

case 1:

    SELECT Count(id) FROM `table`

    WHERE day = 'Monday' and date = '2018-10-15' and time = '19:45:34'

    //get the count

    if ($count != '0')

        echo "Title is incorrect";

case 2:

    SELECT Count(id) FROM `table`

    WHERE day = 'Monday' and date = '2018-10-15' and title = 'NEW-BOTTLE'

    //get the count

    if ($count != '0')

        echo "Time didn't match";

case 3:

    SELECT Count(id) FROM `table`

    WHERE day = 'Monday' and time = '19:45:34' and title = 'NEW-BOTTLE'

    if ($count != '0')

        echo "Date didn't match";

case 4:

    SELECT Count(id) FROM `table`

    WHERE date = '2018-10-15' and time = '19:45:34' and title = 'NEW-BOTTLE'

    if ($count != '0')

        echo "Day didn't match";

}