if group_concat() has a length limit, how to write SQL statement to replace it - wwq0912/mysql GitHub Wiki

	CREATE TABLE IF NOT EXISTS `student` (
	  `ID` int(11) NOT NULL AUTO_INCREMENT,
	  `CLASS_ID` int(11) NOT NULL,
	  `STUDENT_AGE` int(11) NOT NULL,
	  `STUDENT_NAME` varchar(45) DEFAULT NULL,
	  PRIMARY KEY (`ID`),
	  KEY `FK_CLASS_ID` (`CLASS_ID`)
	) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

INSERT INTO `student` (`ID`, `CLASS_ID`, `STUDENT_AGE`, `STUDENT_NAME`) VALUES ('1', '1', '1', 'student1');
INSERT INTO `student` (`CLASS_ID`, `STUDENT_AGE`, `STUDENT_NAME`) VALUES ('1', '1', 'student2');
INSERT INTO `student` (`CLASS_ID`, `STUDENT_AGE`, `STUDENT_NAME`) VALUES ('1', '1', 'student3');
INSERT INTO `student` (`CLASS_ID`, `STUDENT_AGE`, `STUDENT_NAME`) VALUES ('1', '2', 'student4');
INSERT INTO `student` (`CLASS_ID`, `STUDENT_AGE`, `STUDENT_NAME`) VALUES ('1', '2', 'student5');

mysql> select * from student;
+----+----------+-------------+--------------+
| ID | CLASS_ID | STUDENT_AGE | STUDENT_NAME |
+----+----------+-------------+--------------+
|  1 |        1 |           1 | student1     |
|  2 |        1 |           1 | student2     |
|  3 |        1 |           1 | student3     |
|  4 |        2 |           2 | student4     |
|  8 |        1 |           2 | student5     |
+----+----------+-------------+--------------+
5 rows in set (0.00 sec)

一个简单的例子建议下什么使用case when end

SQL1:

SELECT STUDENT_NAME,   
case
when CLASS_ID=1 then 'class1'
when CLASS_ID=2 then 'class2'
else 'otherClass'
end 
as className
FROM student;

+--------------+-----------+
| STUDENT_NAME | className |
+--------------+-----------+
| student1     | class1    |
| student2     | class1    |
| student3     | class1    |
| student4     | class2    |
| student5     | class1    |
+--------------+-----------+
5 rows in set (0.00 sec)

下一个需求 把所有class_id相同的student的name拼接起来

SET @student_name:="";
SET @class_id:='';

select * from (

    SELECT @student_name:= 
    CASE 
    
    WHEN @class_id=class_id 
    
    THEN concat(@student_name, ', ' ,student_name)
    
    ELSE student_name
    END 
    AS student_name, @class_id:=class_id AS className
    FROM student 
    order by class_id
) as temp
;

把set变量的语句简化一下

select * from (

    SELECT @student_name:= 
    
    CASE 
    
    WHEN @class_id=class_id 
    
    THEN concat(@student_name, ', ' ,student_name)
    
    ELSE student_name
    END 
    AS student_name, @class_id:=class_id AS className
    FROM student cross join (select @student_name:='', @class_id:='') init
    order by class_id
) as temp
;

+----------------------------------------+-----------+
| student_name                           | className |
+----------------------------------------+-----------+
| student1                               |         1 |
| student1, student2                     |         1 |
| student1, student2, student3           |         1 |
| student1, student2, student3, student5 |         1 |
| student4                               |         2 |
+----------------------------------------+-----------+
5 rows in set (0.00 sec)

如果要只取每个class的一条

select MAX(student_name) as studentNames,className  from (

    SELECT @student_name:= 
    
    CASE 
    
    WHEN @class_id=class_id 
    
    THEN concat(@student_name, ', ' ,student_name)
    
    ELSE student_name
    END 
    AS student_name, @class_id:=class_id AS className
    FROM student cross join (select @student_name:='', @class_id:='') init
    order by class_id 
) as temp
group by className
;

+----------------------------------------+-----------+
| studentNames                           | className |
+----------------------------------------+-----------+
| student1, student2, student3, student5 |         1 |
| student4                               |         2 |
+----------------------------------------+-----------+
2 rows in set (0.00 sec)