在聚合函数中使用case when

• 测试建表

CREATE TABLE `stu_club`  (
  `id` int(11) NOT NULL,
  `stu_id` int(11) NULL DEFAULT NULL,
  `club_id` int(11) NULL DEFAULT NULL,
  `stu_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `club_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `main_club_flag` tinyint(4) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

• 测试数据

INSERT INTO `stu_club` VALUES (1, 100, 1, '张三', '棒球', 1);
INSERT INTO `stu_club` VALUES (2, 100, 2, '张三', '管弦乐', 0);
INSERT INTO `stu_club` VALUES (3, 200, 2, '李四', '管弦乐', 0);
INSERT INTO `stu_club` VALUES (4, 200, 3, '李四', '羽毛球', 1);
INSERT INTO `stu_club` VALUES (5, 200, 4, '李四', '足球', 0);
INSERT INTO `stu_club` VALUES (6, 300, 4, '王五', '足球', 0);
INSERT INTO `stu_club` VALUES (7, 400, 5, '赵六', '游泳', 0);
INSERT INTO `stu_club` VALUES (8, 500, 6, '孙七', '围棋', 0);

• 需求:
  有的学会加入了多个社团，有的只是加入了某个社团，对于加入了多个社团的学生，我们通过将其“主社团标志”列设置为1或者0来表明哪个社团是他的主社团，对于只是加入了一个社团的学生，我们将其“主社团标志”列设置为0。
  1.如果学生只是加入了一个社团，则显示该学生id和社团id
  2.如果学生加入了多个社团，则显示该学生id和主社团id

select stu_id,
case when count(id)=1 then max(club_id)
else max(case when main_club_flag=1 then club_id else null end) end as 'club_id'
from stu_club group by stu_id

• 等价实现

SELECT stu_id, MAX(club_id) AS 'main_club'
FROM stu_club
GROUP BY stu_id
HAVING COUNT(*) = 1
UNION 
SELECT stu_id, club_id AS 'main_club'
FROM stu_club 
WHERE main_club_flag = 1;