mysql - 如何将 CASE 语句放在 GROUP BY 子句中

Question

我有一个表，其中有一列是用逗号分隔的可能值列表。我想查询，按每个可能的值分组。

作为测试，我编写了这个查询:

SELECT
    `Please_identify_which_of_the_following_classroom_hardware_you_c2`,
    count(`_How_would_you_rate_your_overall_skill_in_using_educational_tec1`) as count,
    `_How_would_you_rate_your_overall_skill_in_using_educational_tec1`
FROM
    `data_Copy_of_Faculty_survey_on_technology_in_the_classroom_Respo`
GROUP BY
    `_How_would_you_rate_your_overall_skill_in_using_educational_tec1`,
    CASE
        WHEN `Please_identify_which_of_the_following_classroom_hardware_you_c2` LIKE '%Elmo%' THEN 'Elmo'
    END

(请原谅列名，它们是自动生成的)

我知道 CASE 语句此时并不是非常有用，但我只是想让查询运行。我收到一个错误:

ERROR 1064 (42000): 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 'THEN 'Elmo' END' at line 10

我一辈子都找不到查询有什么问题。任何见解将不胜感激。

编辑:我试过使用单引号和双引号 - 无论使用何种引号，都会出现同样的问题。

更新:正如 Mark 所指出的，即使我解析了这个查询，结果也不是我想要的。我仍然很好奇为什么这不解析，但查询不是我最初问题的解决方案。

Answer 1

您遇到问题的原因是您的 GROUP BY 属性与 SELECT 属性不一致。

作为MySql docs put it :

"SQL92 and earlier does not permit queries for which 
the select list, HAVING condition, or ORDER BY list refer 
to nonaggregated columns that are neither named in the GROUP BY 
clause nor are functionally dependent on (uniquely determined by)
GROUP BY columns"

换句话说，由于 ...c2 属性“在功能上不依赖于”您的 CASE ...END 属性，因此 之间存在不匹配>SELECT 和 GROUP BY，因此出错。

减轻错误(并可能使查询更具可读性)的一种方法是执行一次 CASE，然后对结果关系执行聚合。

SELECT c2, tec1, COUNT(tec1)
FROM  
    (SELECT
       CASE 
         WHEN `Please_identify_which_of_the_following_classroom_hardware_you_c2` LIKE '%Elmo%' 
           THEN 'Elmo'
         ELSE
         `Please_identify_which_of_the_following_classroom_hardware_you_c2`
       END AS c2,
      `_How_would_you_rate_your_overall_skill_in_using_educational_tec1`) AS tec1
    FROM 
      `data_Copy_of_Faculty_survey_on_technology_in_the_classroom_Respo`) t
GROUP BY c2, tec1