求教mysql分组排序

[yoyo_]

SELECT
`arrangement`.`projectid`,
`arrangement`.`courseid`,
`arrangement_date`.`date`,
SUM(CASE WHEN `arrangement`.`traindays`=0 THEN `arrangement`.`time` ELSE `arrangement`.`traindays` END) AS `usetime`,
GROUP_CONCAT(`teacher`.`name` ORDER BY `teacher`.`name` ASC) AS `teachername`
FROM
`arrangement`
INNER JOIN
`arrangement_date`
ON DATE(`arrangement_date`.`date`) BETWEEN DATE(`arrangement`.`startdate`) AND DATE(`arrangement`.`enddate`)
INNER JOIN
`teacher`
ON `teacher`.`id`=`arrangement`.`teacherid`
WHERE
DATE(`arrangement_date`.`date`) BETWEEN DATE('2015-1-1') AND date('2015-1-31')
GROUP BY
`arrangement`.`projectid`,`arrangement`.`courseid`, `arrangement_date`.`date`
现有sql语句如上,执行后结果如下图：

想实现把分组后的结果按时间（date列）升序排序,于是追加 order by `arrangement_date`.`date`语句，结果（projectid列）分组
被打乱了，执行结果如下图：

不是想要的效果。
请教各位达人，要怎样改进sql，才能实现下图效果 ?

[rick-he]

试下group by projectid order by 时间

[rick-he]

试下group by projectid 时间 order by 时间

[yoyo_]

引用 4 楼 ACMAIN_CHM 的回复:

select a.*
from(
SELECT
`arrangement`.`projectid`,
`arrangement`.`courseid`,
`arrangement_date`.`date`,
SUM(CASE WHEN `arrangement`.`traindays`=0 THEN `arrangement`.`time` ELSE `arrangement`.`traindays` END) AS `usetime`,
GROUP_CONCAT(`teacher`.`name` ORDER BY `teacher`.`name` ASC) AS `teachername`
FROM
`arrangement`
INNER JOIN
`arrangement_date`
ON DATE(`arrangement_date`.`date`) BETWEEN DATE(`arrangement`.`startdate`) AND DATE(`arrangement`.`enddate`)
INNER JOIN
`teacher`
ON `teacher`.`id`=`arrangement`.`teacherid`
WHERE
DATE(`arrangement_date`.`date`) BETWEEN DATE('2015-1-1') AND date('2015-1-31')
GROUP BY
`arrangement`.`projectid`,`arrangement`.`courseid`, `arrangement_date`.`date`
) a
order by (select min(`date`) from (
SELECT
`arrangement`.`projectid`,
`arrangement`.`courseid`,
`arrangement_date`.`date`,
SUM(CASE WHEN `arrangement`.`traindays`=0 THEN `arrangement`.`time` ELSE `arrangement`.`traindays` END) AS `usetime`,
GROUP_CONCAT(`teacher`.`name` ORDER BY `teacher`.`name` ASC) AS `teachername`
FROM
`arrangement`
INNER JOIN
`arrangement_date`
ON DATE(`arrangement_date`.`date`) BETWEEN DATE(`arrangement`.`startdate`) AND DATE(`arrangement`.`enddate`)
INNER JOIN
`teacher`
ON `teacher`.`id`=`arrangement`.`teacherid`
WHERE
DATE(`arrangement_date`.`date`) BETWEEN DATE('2015-1-1') AND date('2015-1-31')
GROUP BY
`arrangement`.`projectid`,`arrangement`.`courseid`, `arrangement_date`.`date`
) b where projectid=a.projectid),a.projectid

前辈， 执行了您的sql语句，结果如下:

但是有点小问题，也是我疑惑的地方，理论上整体是升序排序的，但实际分组内的时间却没有升序排序,比如：projectid为15的地方, date的顺序为啥就乱了?

[yoyo_]

引用 2 楼 u011575570 的回复:

试下group by projectid 时间 order by 时间

恩，试过了，时间是升序排序了，但是projectid 列分组还是被打乱了

[ACMAIN_CHM]

select a.* from( SELECT `arrangement`.`projectid`, `arrangement`.`courseid`, `arrangement_date`.`date`, SUM(CASE WHEN `arrangement`.`traindays`=0 THEN `arrangement`.`time` ELSE `arrangement`.`traindays` END) AS `usetime`, GROUP_CONCAT(`teacher`.`name` ORDER BY `teacher`.`name` ASC) AS `teachername` FROM `arrangement` INNER JOIN `arrangement_date` ON DATE(`arrangement_date`.`date`) BETWEEN DATE(`arrangement`.`startdate`) AND DATE(`arrangement`.`enddate`) INNER JOIN `teacher` ON `teacher`.`id`=`arrangement`.`teacherid` WHERE DATE(`arrangement_date`.`date`) BETWEEN DATE('2015-1-1') AND date('2015-1-31') GROUP BY `arrangement`.`projectid`,`arrangement`.`courseid`, `arrangement_date`.`date` ) a order by (select min(`date`) from ( SELECT `arrangement`.`projectid`, `arrangement`.`courseid`, `arrangement_date`.`date`, SUM(CASE WHEN `arrangement`.`traindays`=0 THEN `arrangement`.`time` ELSE `arrangement`.`traindays` END) AS `usetime`, GROUP_CONCAT(`teacher`.`name` ORDER BY `teacher`.`name` ASC) AS `teachername` FROM `arrangement` INNER JOIN `arrangement_date` ON DATE(`arrangement_date`.`date`) BETWEEN DATE(`arrangement`.`startdate`) AND DATE(`arrangement`.`enddate`) INNER JOIN `teacher` ON `teacher`.`id`=`arrangement`.`teacherid` WHERE DATE(`arrangement_date`.`date`) BETWEEN DATE('2015-1-1') AND date('2015-1-31') GROUP BY `arrangement`.`projectid`,`arrangement`.`courseid`, `arrangement_date`.`date` ) b where projectid=a.projectid),a.projectid

[ACMAIN_CHM]

`arrangement`.`projectid`,`arrangement`.`courseid`, `arrangement_date`.`date` ) b where projectid=a.projectid),a.projectid,a.`date`