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

MySql 码拜 6年前 (2015-09-21) 756次浏览

  commissioner 表如下:

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

  arrangement 表如下:

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

 现有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;
 

  执行结果如下图所示:

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

  想实现GROUP_CONCAT(“”`arrangement`.`teacherid`””)之后的teacherids不重复,且usetime应该以teacherids的个数为根据来进行求和,即:teacherids去重复后,如果teacherids为单个数字,没有逗号分隔,则usetime对应的应该是1,而不是上图中的2。

  当然,GROUP_CONCAT(DISTINCT `arrangement`.`teacherid`)可以去重复,但usetime却不是想要 的效果。

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

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

  

  

 

#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后字段的求和问题
喜欢 (0)
[1034331897@qq.com]
分享 (0)