使用 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' 不为空.这给了我这个图像,它显示了所有空值和我正在寻找的记录.非空时数据库的图片
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屋!