gpt4 book ai didi

mysql - 如何将字符串转换为整数并比较指定数字使用mysql?

转载 作者:行者123 更新时间:2023-11-30 23:08:39 25 4
gpt4 key购买 nike

我使用 sql 获取 text 值,并将 text 字符串转换为整数以比较相同的指定数字。原谅我糟糕的英语。第 1 步,我使用此 sql 获取 text 值。(结果类似于 '8000mAh','2000mAh')

SELECT `product_id`, `text` 
FROM `jfw_product_attribute`
WHERE `attribute_id` =3

第 2 步,我使用 sql 将 text 字符串转换为整数来比较相同的指定数字:(我删除了最后三个 str 'mAh')

SELECT `product_id` ,
CASE
WHEN cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int) <1000
THEN 1
WHEN 1000 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int) <=2000
THEN 2
WHEN 2001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int) <=3000
THEN 3
WHEN 3001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int) <=4000
THEN 4
WHEN 4001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int) <=5000
THEN 5
WHEN 5001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int) <=8000
THEN 6
WHEN 8001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int)
THEN 7
END AS `filter_id`
FROM `jfw_product_attribute`
WHERE `attribute_id` =3

我无法得到正确的 filter_id,我得到的所有 filter_id 都是 2。有人可以帮助我吗?非常感谢。

最佳答案

您可以按照以下方式执行 case语句将匹配第一个可能的过滤器,因此您需要做的就是根据 <= 进行匹配

SELECT `product_id` ,
CASE
WHEN cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int) <1000
THEN 1
WHEN cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int) <=2000
THEN 2
WHEN cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int) <=3000
THEN 3
WHEN cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int) <=4000
THEN 4
WHEN cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int) <=5000
THEN 5
WHEN cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int) <=8000
THEN 6
WHEN 8001 <= cast( REVERSE( SUBSTRING( REVERSE( text ) , 4 ) ) as UNSIGNED int)
THEN 7
END AS `filter_id`
FROM `jfw_product_attribute`
WHERE `attribute_id` =3

关于mysql - 如何将字符串转换为整数并比较指定数字使用mysql?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/20735900/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com