mysql - 根据条件输出没有重复日期的行

Question

为了进行工作量预测，我需要结合“预订时间”(此处:sapactivities)和“计划时间”(此处:plannedac​​tivities)表。输出应包含独特的日期/资源/通知组合所花费的每日总小时数，并优先考虑“预订”时间而不是“计划”时间。

SELECT date_format(result.CurrentDate, '%a, %d.%m.%Y'), 
   result.Resource, result.Notification, result.Hours, result.Effort 
FROM
(SELECT sapactivities.start AS 'CurrentDate',
        sapactivities.activitytext AS 'Resource',
        sapactivities.notification AS 'Notification',
        SUM(TIMESTAMPDIFF(MINUTE,start,end)/60) AS 'Hours',
        'Booked' AS 'Effort'
    FROM work.sapactivities 
    GROUP BY sapactivities.notification,sapactivities.activitytext,sapactivities.start

UNION ALL

SELECT plannedactivities.activitydate AS 'CurrentDate',
    /*CONCAT_WS(' ',resource.firstname,resource.lastname) AS 'Resource',*/
    resource.sapActivityText AS 'Resource',
    notification.notification AS 'Notification',
    plannedactivities.hours AS 'Hours',
    'Planned' AS 'Effort'
FROM work.plannedactivities
LEFT JOIN work.notification ON plannedactivities.idNotification = notification.id
LEFT JOIN work.resource ON plannedactivities.idResource = resource.id) AS result
GROUP BY result.Resource, result.Notification, DATE (result.CurrentDate), 
  CASE WHEN result.Effort = 'Booked' THEN 'Booked' ELSE 'Planned' END
ORDER BY result.Resource,result.CurrentDate ASC;

但是我仍然收到“已预订”和“已计划”工作的日/资源/通知的重复行。我在外部 GROUP BY 子句上尝试了多种方法，但没有得到我正在寻找的结果......有什么想法吗？

Answer 1

通常可以通过 ROW_NUMBER 模拟来完成，请参阅 http://www.mysqltutorial.org/mysql-row_number/ 。提供'Booked'<'Planned'和Hours永远不要超过 1000000 这个技巧就可以了

Select CurrentDate, Resource, Notification, min(Hours) MOD 100000, min(Effort) 
FROM (

    SELECT sapactivities.start AS 'CurrentDate',
            sapactivities.activitytext AS 'Resource',
            sapactivities.notification AS 'Notification',
            1000000 + SUM(TIMESTAMPDIFF(MINUTE,start,end)/60) AS 'Hours',
            'Booked' AS 'Effort'
        FROM work.sapactivities 
        GROUP BY sapactivities.notification,sapactivities.activitytext,sapactivities.start

    UNION ALL

    SELECT plannedactivities.activitydate AS 'CurrentDate',
        /*CONCAT_WS(' ',resource.firstname,resource.lastname) AS 'Resource',*/
        resource.sapActivityText AS 'Resource',
        notification.notification AS 'Notification',
        2000000 + plannedactivities.hours AS 'Hours',
        'Planned' AS 'Effort'
    FROM work.plannedactivities
    LEFT JOIN work.notification ON plannedactivities.idNotification = notification.id
    LEFT JOIN work.resource ON plannedactivities.idResource = resource.id

) AS result
GROUP BY result.Resource, result.Notification, DATE (result.CurrentDate)