mysql - CASE WHEN 不返回预期值

Question

我有一个表，其中有一列包含出生日期 (bdate) 和更晚的日期 (entry)。当一个人的年龄低于 35 岁时，我希望值为 2当年龄 >= 36 且 <= 40 时，我希望值为 3，依此类推。

但是，我的查询从未返回高于 3 的值。有什么问题吗？

SELECT TIMESTAMPDIFF( YEAR, str_to_date( CONCAT( 19, bdate ) , '%Y %m %d' ) , entry),
CASE
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) <= 35 THEN 2
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) >= 36 <= 40 THEN 3
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) >= 41 <= 45 THEN 4
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) >= 46 <= 50 THEN 5
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) >= 51 <= 55 THEN 6
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) >= 56 <= 60 THEN 7
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) >= 61 <= 65 THEN 8
ELSE 0
END AS value
FROM mytable

Answer 1

您需要分离不等式。这是一个例子:

SELECT @a := TIMESTAMPDIFF( YEAR, str_to_date( CONCAT( 19, bdate ) , '%Y %m %d' ) , entry),
CASE
    WHEN @a <= 35 THEN 2
    WHEN @a >= 36 and @a <= 40 THEN 3
    WHEN @a >= 41 and @a <= 45 THEN 4
    WHEN @a >= 46 and @a <= 50 THEN 5
    WHEN @a >= 51 and @a <= 55 THEN 6
    WHEN @a >= 56 and @a <= 60 THEN 7
    WHEN @a >= 61 and @a <= 65 THEN 8
    ELSE 0
END AS value
FROM mytable

使用临时变量 (@a) 的小技巧可以让您省去大量的打字工作(并且让事情变得更清晰)。

希望对你有帮助