mysql - 根据MySQL中的特殊情况查找列的平均长度

我有一个用户表和他们发送的消息的另一个表，我正在尝试查找消息的平均长度和如果用户退休的消息的平均长度。如果我尝试这样做：

SELECT u.id,
AVG(LENGTH(m.body)) AS avg_msg_length,
AVG(LENGTH(CASE m.is_retired WHEN true THEN m.body ELSE NULL END)) AS avg_msg_length_retired
FROM Users u LEFT OUTER JOIN Messages m
ON u.id = m.sender_id
GROUP BY u.id;

结果，我得到的平均长度很好，但是退休人员的平均长度只是一列NULL。

如果我尝试这样做：

SELECT u.id,
AVG(LENGTH(m.body)) AS avg_msg_length,
AVG(LENGTH(CASE m.is_retired WHEN "true" THEN m.body ELSE NULL END)) AS avg_msg_length_retired
FROM Users u LEFT OUTER JOIN Messages m
ON u.id = m.sender_id
GROUP BY u.id;

我明白了


  错误1054（42S22）：“字段列表”中的未知列“ true”。


我发现这非常奇怪，因为is_retired字段是varchar。

我是否在LENGTH中错误地使用了CASE WHEN？我也尝试了CASE WHEN的第二种形式：

 AVG(LENGTH(CASE WHEN m.is_retired=true THEN m.body ELSE NULL END)) AS avg_msg_length_retired

和，

AVG(LENGTH(CASE WHEN m.is_retired="true" THEN m.body ELSE NULL END)) AS avg_msg_length_retired

并获得与上述相同的结果。

最佳答案

我更喜欢length()中的case：

SELECT u.id,
       AVG(LENGTH(m.body)) AS avg_msg_length,
       AVG(CASE WHEN m.is_retired = 'Y' THEN LENGTH(m.body) END) AS avg_msg_length_retired
FROM Users u LEFT OUTER JOIN
     Messages m
     ON u.id = m.sender_id
GROUP BY u.id;

'Y'应该是表示真值的任何字符或字符串。

查询的问题是您要混合使用字符表达式（is_retired）和数字表达式（true）。与类型保持一致。

例如，它的计算结果为false：

 select 'true' = true;

但这评估为true：

select 1 = true;