MYSQL 查询时选择大小写

Question

我正在尝试执行下面的mysql查询，我看不出我做错了什么，我期望从查询的WHEN部分得到结果，而我从ELSE部分得到结果。

SELECT 
   CASE 
   WHEN `club_name` = '7' THEN (
      SELECT group_concat(DISTINCT mobile) from member WHERE `club_name` = '7'
   ) 
   ELSE (SELECT group_concat(mobile) from member) 
   END AS mobile 
FROM member

提前致谢。

Answer 1

我相信这是一起被mysqls extensions to the group by statement烧伤的案例:

选择列表可以引用未在 GROUP BY 子句中命名的非聚合列

...服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则所选择的值是不确定的。 ...

SELECT 
   CASE 
   WHEN `club_name` = '7' THEN (
      SELECT group_concat(DISTINCT mobile) from member WHERE `club_name` = '7'
   ) 
   ELSE (SELECT group_concat(mobile) from member) 
   END AS mobile 
FROM member
GROUP BY mobile

SQL Fiddle

WITH GROUP BY 返回(我相信这就是您想要的)

MOBILE
abc,def,def,xyz,xyz,xyz,lmn
abc,def,def,xyz,xyz,xyz,lmn
xyz,lmn
abc,def,def,xyz,xyz,xyz,lmn

没有分组依据:

MOBILE
abc,def,def,xyz,xyz,xyz,lmn
abc,def,def,xyz,xyz,xyz,lmn
abc,def,def,xyz,xyz,xyz,lmn
abc,def,def,xyz,xyz,xyz,lmn
xyz,lmn
xyz,lmn
xyz,lmn

只返回 1 行...

SELECT 
   CASE 
   WHEN `club_name` = '7' THEN (
      SELECT group_concat(DISTINCT mobile) from member WHERE `club_name` = '7'
   ) 
   ELSE (SELECT group_concat(mobile) from member) 
   END AS mobile 
FROM member
WHERE `club_name` = '7'
GROUP BY mobile