|
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语句呢？
|
|
#15分 |
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` ; |
#355分 |
…….自己的贴，自己都不能该 !@#$%$&
求和的那句，也放到外层去. 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后字段的求和问题！
