mysql - 选择名称，其中案例值>另一个案例值+ 2

此查询的最后部分有些麻烦。

我正在寻找的是哪里capvalue
意思是如果capValue = 1，我正在寻找3或更大的期望的CapValue。

SELECT instanceName, capacitySizeName, desiredCapacitySizeName, desiredCapValue, capvalue FROM
(SELECT instance_name AS instanceName, capacity_size_name AS capacitySizeName,
(CASE capacity_size_name WHEN 'small' THEN '1' WHEN 'medium' THEN '2' WHEN 'large' THEN '3' WHEN 'xlarge' THEN '4' WHEN 'xxlarge' THEN '5' WHEN 'mega' THEN '6' WHEN 'ultra' THEN '7' WHEN 'giga' THEN '8' WHEN 'tera' THEN '9' WHEN 'peta' THEN '10' END) AS `capValue`,
u_desired_capacity_size_name AS desiredCapacitySizeName,
CASE u_desired_capacity_size_name WHEN 'small' THEN '1' WHEN 'medium' THEN '2' WHEN 'large' THEN '3' WHEN 'xlarge' THEN '4' WHEN 'xxlarge' THEN '5' WHEN 'mega' THEN '6' WHEN 'ultra' THEN '7' WHEN 'giga' THEN '8' WHEN 'tera' THEN '9' WHEN 'peta' THEN '10' END AS `desiredCapValue`
FROM mysql_view
WHERE used_for = 'Production')A
WHERE capvalue < desiredCapValue+2 LIMIT 10

提前致谢！

最佳答案

您需要数字（而不是字符串）来获得有效的算术评估和正确的上限值

  SELECT instanceName
    , capacitySizeName
    , desiredCapacitySizeName
    , desiredCapValue
    , capvalue
  FROM (
      SELECT instance_name AS instanceName
      , capacity_size_name AS capacitySizeName
      ,  (CASE capacity_size_name
            WHEN 'small' THEN 1
            WHEN 'medium' THEN 2
            WHEN 'large' THEN 3
            WHEN 'xlarge' THEN 4
            WHEN 'xxlarge' THEN 5
            WHEN 'mega' THEN 6
            WHEN 'ultra' THEN 7
            WHEN 'giga' THEN 8
            WHEN 'tera' THEN 9
            WHEN 'peta' THEN 10 END) AS `capValue`
      , u_desired_capacity_size_name AS desiredCapacitySizeName
      , CASE u_desired_capacity_size_name
            WHEN 'small' THEN 1
            WHEN 'medium' THEN 2
            WHEN 'large' THEN 3
            WHEN 'xlarge' THEN 4
            WHEN 'xxlarge' THEN 5
            WHEN 'mega' THEN 6
            WHEN 'ultra' THEN 7
            WHEN 'giga' THEN 8
            WHEN 'tera' THEN 9
            WHEN 'peta' THEN 10 END AS `desiredCapValue`
  FROM mysql_view
  WHERE used_for = 'Production'
  ) A
  WHERE capvalue < desiredCapValue+2
  LIMIT 10