我在表中有这些数据:
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
,如果有一个没有value
的numb
(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/