MySQL 计数 IF 和 JOIN

Question

我有两个表:

1. 书 table :|病例编号 |解决于(日期) |标签 |

2. ringcentral: |日期 |类型 |操作结果 |

我的目标是在过去的 7 天内获得以下输出:

| Date | Count of TotalResolvedCases | Count of Total Calls |

我需要在两个单独的表中计算案例和调用，然后按日期加入和分组计数。我还在处理两个表中不同的日期格式，因此古怪的 group by 和 join 函数。

我还需要让连接包含前 7 天的所有日期(deskcases 包含所有日期，所以我认为 INNER JOIN 应该在这里工作？)

为了结束，我尝试在下面运行以下查询，但它只是在一两分钟后没有结果。关于如何让它工作的任何想法？预先感谢您的帮助! :-)

(运行 MySQL 5.0.96)

SELECT
DATE_FORMAT(
    deskcases.`Resolved At`,
    '%Y-%m-%d'
)AS Date1,
(
    SELECT
        COUNT(deskcases.`Case #`)
    FROM
        deskcases
    WHERE
        deskcases.Labels NOT LIKE '%SPAM%'
    AND deskcases.Labels NOT LIKE '%Online Orders%'
    AND deskcases.Labels NOT LIKE '%Internal SPAM%'
    AND deskcases.`Resolved At` > NOW()- INTERVAL 8 DAY
)AS TotalResolvedCases,
(
    SELECT
        COUNT(ringcentral.Counter)
    FROM
        ringcentral
    WHERE
        `ringcentral`.`Type` = 'Voice'
    AND `Action Result` = 'Accepted'
    AND ringcentral.`Date` > NOW()- INTERVAL 8 DAY
)AS TotalCalls
FROM
 deskcases
INNER JOIN ringcentral ON DATE_FORMAT(
 deskcases.`Resolved At`,
 '%Y-%m-%d'
)= ringcentral.Date
GROUP BY
 Date1
ORDER BY
 Date1 ASC

编辑

我能够稍微编辑它以得到一些结果，但似乎我正在计算所有案例和调用我的子选择查询并且总数没有按日期分组。这是代码:

SELECT
DATE_FORMAT(
    deskcases.`Resolved At`,
    '%Y-%m-%d'
)AS Date1,
(
    SELECT
        COUNT(deskcases.`Case #`)
    FROM
        deskcases
    WHERE
        deskcases.Labels NOT LIKE '%SPAM%'
    AND deskcases.Labels NOT LIKE '%Online Orders%'
    AND deskcases.`Case Status` LIKE '%Resolved%'
)AS TotalResolvedCases,
(
    SELECT
        COUNT(ringcentral.Counter)
    FROM
        ringcentral
    WHERE
        `ringcentral`.`Type` = 'Voice'
    AND `Action Result` = 'Accepted'
)AS TotalCalls
FROM
  deskcases
LEFT OUTER JOIN ringcentral ON DATE_FORMAT(
  deskcases.`Resolved At`,
  '%Y-%m-%d'
)= ringcentral.Date
WHERE deskcases.`Resolved At` > NOW()- INTERVAL 8 DAY
GROUP BY
  Date1
ORDER BY
  Date1 ASC

Answer 1

在这种情况下，您绝对不应该参加约会。

例如，您可能拥有以下数据:

deskcases                           ringcentral
---------                           -----------
date      | whatever_d              date      | whatever_r
----------------------              ----------------------
today       x                       yesterday   u
yesterday   y
yesterday   z

现在，当你加入他们时，你会得到:

joinedTable
-----------
date      | whatever_d  |  whatever_r
-------------------------------------
today       x 
yesterday   y              u
yesterday   z              u

糟糕，您从铃声表中获得了 2 次条目，但您只想要 1 次。

那么，您如何获取数据？要么写 2 条语句，要么将它们与 UNION 组合起来:

SELECT
DATE(`Resolved At`) AS Date1,
"Deskcases" AS fromTable,
COUNT(*) AS TotalNumber
FROM
        deskcases
    WHERE
        deskcases.Labels NOT LIKE '%SPAM%'
    AND deskcases.Labels NOT LIKE '%Online Orders%'
    AND deskcases.Labels NOT LIKE '%Internal SPAM%'
    AND deskcases.`Resolved At` > CURDATE()- INTERVAL 8 DAY
GROUP BY 1, 2
UNION ALL
SELECT
`Date` AS Date1,
"ringcentral" AS fromTable,
COUNT(*) AS TotalNumber
FROM
        ringcentral
    WHERE
        `ringcentral`.`Type` = 'Voice'
    AND `Action Result` = 'Accepted'
    AND ringcentral.`Date` > CURDATE()- INTERVAL 8 DAY
GROUP BY 1, 2

另请注意，我将 NOW() 更改为 CURDATE()。在 deskcases 表中，您的日期列似乎是一个时间戳。当您从“2013-03-20 10:06:00”中减去 8 天时，您将得到“2013-03-12 10:06:00”，因此您将丢失“2013-03-12”之间的所有行00:00:00' 和 '2013-03-12 10:05:59'。此外，我只是使用了 DATE() 函数而不是 DATE_FORMAT()。 DATE_FORMAT() 在很多方面都是一个非常好的和有用的函数，但在这种情况下，您在使用 DATE() 时必须输入更少的字符 :)

更新:

SELECT 
Date1, 
SUM(CASE WHEN fromTable = 'Deskcases' THEN TotalNumber ELSE 0 END) AS TotalDeskcases,
SUM(CASE WHEN fromTable = 'ringcentral' THEN TotalNumber ELSE 0 END) AS TotalRingcentral 
FROM (
SELECT
DATE(`Resolved At`) AS Date1,
'Deskcases' AS fromTable,
COUNT(*) AS TotalNumber
FROM
        deskcases
    WHERE
        deskcases.Labels NOT LIKE '%SPAM%'
    AND deskcases.Labels NOT LIKE '%Online Orders%'
    AND deskcases.Labels NOT LIKE '%Internal SPAM%'
    AND deskcases.`Resolved At` > CURDATE()- INTERVAL 8 DAY
GROUP BY 1, 2
UNION ALL
SELECT
`Date` AS Date1,
'ringcentral' AS fromTable,
COUNT(*) AS TotalNumber
FROM
        ringcentral
    WHERE
        `ringcentral`.`Type` = 'Voice'
    AND `Action Result` = 'Accepted'
    AND ringcentral.`Date` > CURDATE()- INTERVAL 8 DAY
GROUP BY 1, 2
) sq
GROUP BY Date1