test - wwq0912/mysql GitHub Wiki

https://stackoverflow.com/questions/25912548/mysql-select-statement-from-multiple-statement#

mysql> select * from student;
+----+----------+-------------+--------------+
| ID | CLASS_ID | STUDENT_AGE | STUDENT_NAME |
+----+----------+-------------+--------------+
|  1 |        4 |           1 | student1     |
|  2 |        1 |           1 | student2     |
|  3 |        1 |           1 | student3     |
|  4 |        3 |           2 | student4     |
|  5 |        1 |           1 | student5     |
|  7 |        2 |           2 | student6     |
+----+----------+-------------+--------------+

1: 按降序排

select * from (

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

结果

+-------------------------------------+----------+
| student_name                        | class_id |
+-------------------------------------+----------+
| student1!=2                         |        4 |
| student4!=4                         |        3 |
| student6!=3                         |        2 |
| student2!=2                         |        1 |
| student2!=2, student3=1             |        1 |
| student2!=2, student3=1, student5=1 |        1 |
+-------------------------------------+----------+

2: 按升序排

select * from (

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

结果

+-------------------------------------+----------+
| student_name                        | class_id |
+-------------------------------------+----------+
| student2!=2                         |        1 |
| student2!=2, student3=1             |        1 |
| student2!=2, student3=1, student5=1 |        1 |
| student6!=1                         |        2 |
| student4!=2                         |        3 |
| student1!=3                         |        4 |
+-------------------------------------+----------+

可以看到第一次的@class_id都是2, 所以可以确定的是赋值语句先执行了。。。所以到底是哪条class_id给他赋值的呢？？？ 我的准备数据 class_id为2的那条按class_id 排序（不论升降）一直都会排在中间 所以否定了按order by之后的数据取值。。。所以大胆猜想 应该是student表的最后一条数据的class_id赋值给他了

解决方案2： 将@class_id:=class_id AS class_id 改成 @class_id:=class_id AS class_id_result 就可以正常work了 不会有','

一般的order by是在select之后 但是这种情况下为了取出正确的数据所以必须先执行order by