mysql - 轮函数中 case 表达式和除法运算符的使用错误

Question

如果聊天评级为 4 或 5，则这是一次评级良好的聊天。聊天可分为 1/2/3/4/5。但是，我提交的代码在使用 case 语句时出现错误

(#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'case cs.chat_rating when (4,5) then GR when (1,2,3,4,5) then TR end WHERE cs.ag' at line 4).

此外，我认为我对舍入函数和除法运算符的使用可能是错误的。我需要找到良好评分与总评分的比率。

SELECT Distinct(cm.channel_name), round(GR DIV TR) FROM channel_company AS cc
    LEFT JOIN chat_sessions 
    LEFT JOIN channel_master
    case cs.chat_rating
    when (4,5) then GR 
    when (1,2,3,4,5) then TR end
    WHERE cs.agent_assigned_time >= " "
    AND cs.agent_assigned_time <= " "
    group by 1

Answer 1

SELECT cm.channel_name,
(SUM(case 
    when cs.chat_rating IN (4,5) then 1 ELSE 0 END) / SUM(case when cs.chat_rating IS NOT NULL then 1 ELSE 0 END)) AS ratio_rating 
    FROM
    channel_company as cc 
    LEFT JOIN chat_sessions AS cs ON cs.company_id = cc.company_id
    LEFT JOIN channel_master AS cm ON cc.channel_id = cm.id
    WHERE cs.agent_assigned_time >= '2015-10-28 06:30:00'
    AND cs.agent_assigned_time <= '2015-10-29 02:30:00'
    group by cm.channel_name

您错过了CASE位置和一些IN在WHEN条件。您必须提及 cc 的正确表别名在FROM (你错过了)希望这有帮助