Code Bye

mysql关于使用GROUP_CONCAT后字段的求和问题

码拜
mysql

  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语句呢？

  

  

  

 
#1

5分
sql方面没有想到好办法，只能给个建议

teacherids不要去重复，usetime的SUM换成GROUP_CONCAT，变成1.00,1.00这样。

用后台语言查询出来后，进行遍历，分别拆分teacherids和usetime，根据teacherids拆分项的重复情况，计算对应usetime拆分项加或不加。

当然个人也很期待有高手能用sql解决。
#2
 
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` ;  
#3

55分
…….自己的贴，自己都不能该 !@#$%$&

求和的那句，也放到外层去.

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` ; 
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明mysql关于使用GROUP_CONCAT后字段的求和问题！