mysql - 将满足其他几个列条件的列分组在一起

Question

我需要能够对与其他列中的多个条件匹配的列(小时)进行求和。这就是表格的样子。

payrollid, agentid, ptid, serviceid, dateincurred ,hours
209      , 284    , 580 , 1320     , '2014-05-19' ,4
209      , 284    , 569 , 1433     , '2014-45-19' ,2
209      , 284    , 580 , 1433     , '2014-05-19' ,2
209      , 284    , 569 , 1433     , '2014-05-19' ,2
209      , 284    , 580 , 1320     , '2014-05-19' ,7
209      , 284    , 580 ,   19     , '2014-05-20' ,1
209      , 284    , 569 , 1318     , '2014-45-21' ,2
209      , 284    , 580 , 1320     , '2014-45-22' ,1
209      , 284    , 580 ,   16     , '2014-05-23' ,1
209      , 284    , 569 , 1445     , '2014-45-24' ,9
209      , 284    , 580 , 1427     , '2014-05-25' ,1
209      , 284    , 569 , 1326     , '2014-05-28' ,1
209      , 284    , 569 , 1445     , '2014-45-29' ,4
209      , 284    , 569 , 1320     , '2014-05-29' ,1
209      , 284    , 569 , 1347     , '2014-45-29' ,5
209      , 284    , 580 , 1320     , '2014-05-30' ,1
209      , 284    , 569 , 1347     , '2014-05-30' ,5
209      , 284    , 580 , 1326     , '2014-05-31' ,1
209      , 284    , 580 , 1348     , '2014-06-01' ,1
209      , 284    , 580 ,   24     , '2014-06-01' ,1

查询如下所示:

SELECT
    p.agentid,
    p.ptid,
    p.dateincurred,
    p.serviceid,

    (
    SELECT 
        GROUP_CONCAT(p.hours) 
    FROM payroll_detail_temp p3
    WHERE p3.payrollid = '209'
    AND (
            p3.dateincurred BETWEEN '2014-05-19'
            AND DATE_ADD('2014-05-19', INTERVAL 6 DAY))
            AND p3.serviceid = p.serviceid
            GROUP BY
                p3.serviceid
            LIMIT 1
        ) AS week1,

        (
        SELECT
            GROUP_CONCAT(p2.hours)
        FROM
            payroll_detail_temp p2
        WHERE
            p2.payrollid = '209'
        AND (
            p2.dateincurred BETWEEN '2014-05-25'
            AND DATE_ADD('2014-05-25', INTERVAL 6 DAY))
            AND p2.serviceid = p.serviceid
        GROUP BY
            p2.serviceid,
            p2.dateincurred
        LIMIT 1
    ) AS week2,
    GROUP_CONCAT(p.serviceid) AS services,
    GROUP_CONCAT(p.dateincurred) AS dates
FROM
    payroll_detail_temp p
    INNER JOIN agent ON (p.agentid = agent.id)
    INNER JOIN service ON (p.serviceid = service.id)
    INNER JOIN payer ON (p.payerid = payer.id)
    INNER JOIN prov ON (p.provid = prov.id)
    INNER JOIN patient ON (p.ptid = patient.id)
WHERE
    p.payrollid = '209'
    AND (
        p.dateincurred BETWEEN '2014-05-19'
        AND DATE_ADD('2014-05-25', INTERVAL 14 DAY)
    )
GROUP BY
    p.serviceid,
    p.dateincurred

“第 1 周”和“第 2 周”列代表 2 周内的工作时间，在本例中，它是从 2014 年 5 月 19 日到 2014 年 6 月 1 日设置的。因此，Week1 列将对 agentid、ptid、serviceid 匹配的时间进行分组，并且发生的日期范围为 2014-05-19 到 2014-05-25。对于发生日期在 2014 年 5 月 26 日到 2014 年 6 月 1 日之间的第二周也是如此。

我的查询的问题是我得到了重复项，就像下面的查询结果中显示的最后两个结果一样。 Week1 应为 9.00，Week2 必须为空；同样，Week1 下面的行应该为 null，Week2 应该为 4.00。在第 5 行和第 6 行中也可以看到同样的情况。在第 5 行上，Week1 应该只是 7.00、4.00 和 1.00，而 Week 2 应该为空。第 6 行不应该存在。

我知道存在重复项，因为 dateincurred 字段与查询重叠，但我无法找出它周围的任何其他字段。

Answer 1

如果您有如第一张图片所示的表格。然后你可以通过这个查询轻松实现你想要的:

SELECT
 PAYROLLID,
 AGENTID,
 PTID,
 SERVICEID,
 DATEINCURED,
 CASE WHEN (DATEINCURRED BETWEEN '2014-05-19' AND '2014-05-25') THEN SUM(HOURS) END WEEK1,
 CASE WHEN (DATEINCURRED BETWEEN '2014-05-26' AND '2014-06-01') THEN SUM(HOURS) END WEEK2
FROM
 YOUR_TABLE_NAME
GROUP BY
PAYROLLID,
AGENTID;