求教使用GROUP_CONCAT后字段的求和问题

[yoyo_]

commissioner 表如下：


arrangement 表如下：


现有sql语句如下：

 DROP TABLE IF EXISTS temp_arrangement;
     CREATE TABLE temp_arrangement  
     SELECT   
        `arrangement`.`id`,		
		`arrangement`.`projectid`,
        `arrangement`.`courseid`,
		`arrangement_date`.`date` AS `traindate`,
        GROUP_CONCAT(`arrangement`.`teacherid`  ORDER BY `arrangement`.`teacherid` ASC)  AS `teacherids`,
        `arrangement`.`commissionerids` AS `commissionerids`,
		GROUP_CONCAT(DISTINCT `commissioner`.`name`  ORDER BY `commissioner`.`id` ASC) AS `commissionernames`,
		`arrangement`.`time`,
		`arrangement`.`traindays`,
        SUM(CASE WHEN `arrangement`.`traindays`=0 THEN `arrangement`.`time` ELSE `arrangement`.`traindays` END) AS `usetime`
	FROM 
        `arrangement`
	INNER JOIN 
        `arrangement_date`
    ON 
        DATE(`arrangement_date`.`date`)  BETWEEN  DATE(`arrangement`.`startdate`) AND  DATE(`arrangement`.`enddate`)

	LEFT JOIN 
		`commissioner`
	ON FIND_IN_SET(`commissioner`.`id`,`arrangement`.`commissionerids`) 
  
	WHERE DATE( `arrangement_date`.`date`)  BETWEEN  DATE('2015-07-01')  AND  DATE('2015-07-10')

    GROUP BY  `arrangement`.`projectid`,`arrangement`.`courseid`, `arrangement_date`.`date` ;
    
	SELECT a.* FROM temp_arrangement AS a  ORDER BY 
    (SELECT MIN(b.traindate) FROM temp_arrangement AS b WHERE projectid=a.projectid),a.projectid,a.traindate;

 

执行结果如下图所示：


想实现GROUP_CONCAT('`arrangement`.`teacherid`')之后的teacherids不重复，且usetime应该以teacherids的个数为根据来进行求和，即：teacherids去重复后，如果teacherids为单个数字，没有逗号分隔，则usetime对应的应该是1，而不是上图中的2。
当然，GROUP_CONCAT(DISTINCT `arrangement`.`teacherid`）可以去重复，但usetime却不是想要 的效果。

想请教各位达人，要实现下图这种效果，要怎么改进sql语句呢？

[yoyo_]

引用 3 楼 yangb0803 的回复:

.......自己的贴，自己都不能该 !@#$%$& 求和的那句，也放到外层去.

SELECT TT.*,   
     GROUP_CONCAT(TT.`teacherid`  
     ORDER BY TT.`teacherid` ASC)  AS `teacherids`,
     SUM(CASE TT.`traindays` WHEN 0 THEN TT.`time` 
           ELSE TT.`traindays` END) AS `usetime`  
     FROM
     (   
     SELECT   
        `arrangement`.`id`,       
        `arrangement`.`projectid`,
        `arrangement`.`courseid`,
        `arrangement_date`.`date` AS `traindate`,
        `arrangement`.`commissionerids` AS `commissionerids`,
        `arrangement`.`teacherid`,
        GROUP_CONCAT(DISTINCT `commissioner`.`name`  
            ORDER BY `commissioner`.`id` ASC) AS `commissionernames`,
        `arrangement`.`time`,
        `arrangement`.`traindays`       
     FROM 
        `arrangement`
     INNER JOIN 
        `arrangement_date`
     ON 
        DATE(`arrangement_date`.`date`)  BETWEEN  
            DATE(`arrangement`.`startdate`) AND  DATE(`arrangement`.`enddate`)
 
     LEFT JOIN 
        `commissioner`
     ON FIND_IN_SET(`commissioner`.`id`,`arrangement`.`commissionerids`) 
   
     WHERE DATE( `arrangement_date`.`date`)  BETWEEN  
         DATE('2015-07-01')  AND  DATE('2015-07-10')
 
     GROUP BY `arrangement`.`projectid`,`arrangement`.`courseid`, 
             `arrangement`.`teacherid`, `arrangement_date`.`date`  
     ) AS TT 
GROUP BY TT.`projectid`, TT.`courseid`, 
    TT.`traindate` ; 

多谢前辈解惑！

[yoyo_]

引用 1 楼 ayayad 的回复:

sql方面没有想到好办法，只能给个建议 teacherids不要去重复，usetime的SUM换成GROUP_CONCAT，变成1.00,1.00这样。 用后台语言查询出来后，进行遍历，分别拆分teacherids和usetime，根据teacherids拆分项的重复情况，计算对应usetime拆分项加或不加。 当然个人也很期待有大神能用sql解决。

通过后台语言的话，感觉查询效率太低了，不过还是非常感谢前辈，现已用sql解决了！

[ayayad]

确实可以，学习了

[ayayad]

sql方面没有想到好办法，只能给个建议 teacherids不要去重复，usetime的SUM换成GROUP_CONCAT，变成1.00,1.00这样。 用后台语言查询出来后，进行遍历，分别拆分teacherids和usetime，根据teacherids拆分项的重复情况，计算对应usetime拆分项加或不加。 当然个人也很期待有大神能用sql解决。

[道玄希言]

SELECT TT.*,   
     GROUP_CONCAT(TT.`teacherid`  ORDER BY TT.`teacherid` ASC)  AS `teacherids`  
     FROM
     (   
     SELECT   
        `arrangement`.`id`,       
        `arrangement`.`projectid`,
        `arrangement`.`courseid`,
        `arrangement_date`.`date` AS `traindate`,
        `arrangement`.`commissionerids` AS `commissionerids`,
        `arrangement`.`teacherid`,
        GROUP_CONCAT(DISTINCT `commissioner`.`name`  
            ORDER BY `commissioner`.`id` ASC) AS `commissionernames`,
        `arrangement`.`time`,
        `arrangement`.`traindays`,
        SUM(CASE WHEN `arrangement`.`traindays`=0 THEN `arrangement`.`time` 
           ELSE `arrangement`.`traindays` END) AS `usetime`
     FROM 
        `arrangement`
     INNER JOIN 
        `arrangement_date`
     ON 
        DATE(`arrangement_date`.`date`)  BETWEEN  
            DATE(`arrangement`.`startdate`) AND  DATE(`arrangement`.`enddate`)
 
     LEFT JOIN 
        `commissioner`
     ON FIND_IN_SET(`commissioner`.`id`,`arrangement`.`commissionerids`) 
   
     WHERE DATE( `arrangement_date`.`date`)  BETWEEN  
         DATE('2015-07-01')  AND  DATE('2015-07-10')
 
     GROUP BY `arrangement`.`projectid`,`arrangement`.`courseid`, 
             `arrangement`.`teacherid`, `arrangement_date`.`date`  
     ) AS TT 
GROUP BY TT.`projectid`, TT.`courseid`, 
    TT.`traindate` ;  

[道玄希言]

.......自己的贴，自己都不能该 !@#$%$& 求和的那句，也放到外层去.

SELECT TT.*,   
     GROUP_CONCAT(TT.`teacherid`  
     ORDER BY TT.`teacherid` ASC)  AS `teacherids`,
     SUM(CASE TT.`traindays` WHEN 0 THEN TT.`time` 
           ELSE TT.`traindays` END) AS `usetime`  
     FROM
     (   
     SELECT   
        `arrangement`.`id`,       
        `arrangement`.`projectid`,
        `arrangement`.`courseid`,
        `arrangement_date`.`date` AS `traindate`,
        `arrangement`.`commissionerids` AS `commissionerids`,
        `arrangement`.`teacherid`,
        GROUP_CONCAT(DISTINCT `commissioner`.`name`  
            ORDER BY `commissioner`.`id` ASC) AS `commissionernames`,
        `arrangement`.`time`,
        `arrangement`.`traindays`       
     FROM 
        `arrangement`
     INNER JOIN 
        `arrangement_date`
     ON 
        DATE(`arrangement_date`.`date`)  BETWEEN  
            DATE(`arrangement`.`startdate`) AND  DATE(`arrangement`.`enddate`)
 
     LEFT JOIN 
        `commissioner`
     ON FIND_IN_SET(`commissioner`.`id`,`arrangement`.`commissionerids`) 
   
     WHERE DATE( `arrangement_date`.`date`)  BETWEEN  
         DATE('2015-07-01')  AND  DATE('2015-07-10')
 
     GROUP BY `arrangement`.`projectid`,`arrangement`.`courseid`, 
             `arrangement`.`teacherid`, `arrangement_date`.`date`  
     ) AS TT 
GROUP BY TT.`projectid`, TT.`courseid`, 
    TT.`traindate` ;