MySQL 格式良好的查询不会使用 equal 和 Between 检索任何结果

Question

所以，将这些行放在表格上

## raw_material_costs ##
id    code      family     material min_size    max_size    value
651   100005    sleeve     SAE 1020 1.21        1.24        4.48882
659   100006    sleeve     SAE 1020 1.21        1.24        6.62878

直接在 SQL 选项卡上运行以下查询不会返回任何这些行。如果值(在本例中为 1.21)等于 min_size 或介于 min_size 和 max_size 之间，我想获取该行。 (之前尝试过使用BETWEEN比较，得到了相同的结果)

SELECT * FROM (`raw_material_costs`) 
WHERE `raw_material_costs`.`family` =  'sleeve' 
AND `raw_material_costs`.`material` =  'SAE 1020' 
AND (
    ( `raw_material_costs`.`min_size` = '1.21' )
    OR ( `raw_material_costs`.`min_size` >= '1.21' AND `raw_material_costs`.`max_size` <=   '1.21' )
)

EDIT1:忘记提及所使用的值。

修复了查询(cjg 指出的解决方案[如下所示])

SELECT *
FROM (`raw_material_costs`)
WHERE `raw_material_costs`.`family` =  'sleeve'
AND
`raw_material_costs`.`material` =  'SAE 1020'
AND   (
(
CAST( `min_size` as DECIMAL(10,2) ) =
1.21
  )
OR     (
CAST( `min_size` as DECIMAL(10,2) ) >= '1.21'
AND CAST( `max_size` as
DECIMAL(10,2) ) <= '1.21'
)
)

Answer 1

您将 min_size 和 max_size 视为 SQL 查询中的文本字符串，请尝试从最小和最大大小中删除撇号:

正如 Gorkk 在评论中指出的那样，您还按 1.21 而不是 1.24 过滤了 max_size。

SELECT * FROM (`raw_material_costs`) 
WHERE `raw_material_costs`.`family` =  'sleeve' 
AND `raw_material_costs`.`material` =  'SAE 1020' 
AND (
    ( `raw_material_costs`.`min_size` = 1.21 )
    OR ( `raw_material_costs`.`min_size` >= 1.21 AND `raw_material_costs`.`max_size` <=   1.24 )
)