mysql– 如何在GROUP BY子句中放入CASE语句

概述我有一个表,其中一列是逗号分隔的可能值列表.我想查询,按每个可能的值分组.作为测试,我写了这个查询：SELECT `Please_identify_which_of_the_following_classroom_hardware_you_c2`, count(`_How_would_you_rate_your_overall_skill_i

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

作为测试,我写了这个查询：

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指出的那样,即使我要解析这个查询,结果也不会是我想要的.我仍然很好奇为什么这不解析,但查询不是我最初问题的解决方案.

最佳答案您遇到问题的原因是您的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`) tGROUP BY c2,tec1

总结

以上是内存溢出为你收集整理的mysql – 如何在GROUP BY子句中放入CASE语句全部内容，希望文章能够帮你解决mysql – 如何在GROUP BY子句中放入CASE语句所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。