我在表中有这些数据:

numb    m   value
8070    1   7.63
NULL    1   7.64
NULL    1   7.65
8070    2   7.939
8070    2   7.935
8070    2   7.941
NULL    3   7.62
8070    4   7.92
8070    4   7.935


我需要每个MIN(value)MAX(value)m,如果有一个没有valuenumb(NULL),则应忽略带有numb的那些。

所以我应该得到以下结果:

numb    m   value
NULL    1   7.64
NULL    1   7.65
8070    2   7.935
8070    2   7.941
NULL    3   7.62
8070    4   7.92
8070    4   7.935


我已经尝试了很多不同的方法,但是似乎没有任何效果,而且我也没有找到如何找到相关信息的想法。您能指出我正确的方向吗?

更新:
获取值的数量,看起来像这样:

COALESCE(
IF(
  COUNT(
    CASE
      WHEN m IN (2, 4)
      THEN value
      ELSE
      CASE
        WHEN m IN (1, 3) AND numb IS NULL
        THEN value
      END
    END
  ) = 0,
  NULL,
  COUNT(
    CASE
      WHEN m IN (2, 4)
      THEN value
      ELSE
      CASE
        WHEN m IN (1, 3) AND numb IS NULL
        THEN value
      END
    END
  )
),
COUNT(
  CASE
    WHEN m IN (1, 3)
    AND numb IS NOT NULL
    THEN value
  END
)
) AS cnt

最佳答案

该查询应为您提供所需的结果。它具有两层嵌套的派生表。首先:

SELECT m,
       MIN(CASE WHEN numb IS NULL THEN value END) AS min_null,
       MAX(CASE WHEN numb IS NULL THEN value END) AS max_null,
       MIN(CASE WHEN numb IS NOT NULL THEN value END) AS min_normal,
       MAX(CASE WHEN numb IS NOT NULL THEN value END) AS max_normal
FROM numbers
GROUP BY m;


根据m是数字还是numb计算每个NULL值的最小值和最大值。在下一级别,

SELECT m,
       COALESCE(min_null, min_normal) AS min_value,
       COALESCE(max_null, max_normal) AS max_value
FROM (... query 1...)


我们使用计算适当的最小值和最大值来使用(如果存在NULL值,则使用该值,否则使用与numb的数值相关联的值)。最后,我们对查询表2的结果进行JOIN数字表查找,以找到每个numb值的相应m值:

SELECT n.numb, n.m, n.value
FROM numbers n
JOIN (... query 2 ...) num ON num.m = n.m AND (num.min_value = n.value OR num.max_value = n.value)
ORDER BY n.m, n.value


输出:

numb    m   value
null    1   7.64
null    1   7.65
8070    2   7.935
8070    2   7.941
null    3   7.62
8070    4   7.92
8070    4   7.935


Demo on dbfiddle

完整查询:

SELECT n.numb, n.m, n.value
FROM numbers n
JOIN (SELECT m,
             COALESCE(min_null, min_normal) AS min_value,
             COALESCE(max_null, max_normal) AS max_value
      FROM (SELECT m,
                   MIN(CASE WHEN numb IS NULL THEN value END) AS min_null,
                   MAX(CASE WHEN numb IS NULL THEN value END) AS max_null,
                   MIN(CASE WHEN numb IS NOT NULL THEN value END) AS min_normal,
                   MAX(CASE WHEN numb IS NOT NULL THEN value END) AS max_normal
            FROM numbers
            GROUP BY m) n) num ON num.m = n.m AND (num.min_value = n.value OR num.max_value = n.value)
ORDER BY n.m, n.value

关于mysql - 当 NumPy 为空时,MySQL CASE忽略 NumPy 为非空的记录,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/55022725/

10-11 05:35