使用 CASE 函数时，大于零的 WHERE 子句仍显示为零 [英] WHERE clause great than zero still showing zero when using CASE functions

问题描述

我正在尝试使用 WHERE 子句过滤掉我不需要的其他数据.

I am trying the WHERE clause to filter out other data I dont want.

SELECT `post_id`,
       MAX(CASE WHEN `meta_key` = 'vlaue_1' THEN `meta_value` ELSE NULL END) as 'Customer',
       MAX(CASE WHEN `meta_key` = 'value_2' THEN `meta_value` ELSE NULL  END) as 'DeliveryDate',
        MAX(CASE WHEN `meta_key` = 'value_3' THEN `meta_value` ELSE NULL  END) as 'DeliveryTime',
         MAX(CASE WHEN `meta_key` = 'vlaue_4' THEN `meta_value` ELSE NULL  END) as 'DeliveryType'
FROM wp_postmeta 
WHERE 'Customer' > 0
GROUP BY `post_id`  
ORDER BY `wp_postmeta`.`post_id` ASC

以上返回错误截断不正确的 DOUBLE 值".是的，我已经查了很多地方，包括 stackoverflow，并理解了它的含义，除了这个例子.

The above is returning the error 'Truncated incorrect DOUBLE value'. Yes I have looked this up a number of places including stackoverflow and understand its meaning except not in this instance.

好的，所以我认为我根本不会使用客户".没问题.我会告诉它使用 WHERE 'Customer' IS NOT NULL 来搜索 'Customer' 不为空的时间.这给了我这张图片，它显示所有内容为 null 以及我正在寻找的记录.不为空时的数据库图片

OK so I thought I wouldnt use 'customer' at all. No problem. I will tell it to search for when the 'Customer'is not null using WHERE 'Customer' IS NOT NULL. That gives me this image where it displays everything null AND the records I am seeking. pic of db when not null

我在这里错过了什么?

推荐答案

你的 WHERE 逻辑属于 HAVING 子句:

Your WHERE logic belongs in a HAVING clause:

SELECT
    post_id,
    MAX(CASE WHEN meta_key = 'vlaue_1' THEN meta_value END) AS Customer,
    MAX(CASE WHEN meta_key = 'value_2' THEN meta_value END) AS DeliveryDate,
    MAX(CASE WHEN meta_key = 'value_3' THEN meta_value END) AS DeliveryTime,
    MAX(CASE WHEN meta_key = 'value_4' THEN meta_value END) AS DeliveryType
FROM wp_postmeta 
GROUP BY post_id
HAVING Customer > 0
ORDER BY post_id;

请注意，您当前的 WHERE 子句在技术上可能是有效的 MySQL:

Note that your current WHERE clause might technically be valid MySQL:

WHERE 'Customer' > 0

但是，这会检查字符串文字 'Customer' 是否大于值 0.它确实不实际上检查了客户案例表达式的值，即直到 group by 发生后才可用.

However, this checks whether the string literal 'Customer' is greater than the value 0. It does not actually check the value of the customer case expression, which is not even available until after group by happens.

这篇关于使用 CASE 函数时，大于零的 WHERE 子句仍显示为零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！