我有两个表:
-
deskcases
:| CaseID | Resolved At(Date) | Labels |
-
ringcentral
:| Date | Type | Action Result |
我的目标是在过去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
标题>在这种情况下,您绝对不应该加入日期。
例如:
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
哎呀,您从ringtones表中获得了2次条目,但您只想要1次。
那么,你如何得到你的数据呢?写两个语句或将它们与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"one_answers"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