mysql - 将相关 mysql 子查询转换为非相关

Question

我需要帮助将相关子查询转换为非相关子查询，这是一个查询:

http://sqlfiddle.com/#!2/2a67b/3

感谢您的建议

Answer 1

我真的很难弄清楚你的 SQL 首先想要做什么。

它似乎正在获取最小秒和最大秒之间的每一秒(嗯，比最大日期时间多 1 秒)，并获取与之相关的记录计数。

如果是这样，则最多处理 1000 秒:-

SELECT ADDDATE(MinCallDate, INTERVAL Sub1.i SECOND) AS aDate, COUNT(*)
FROM 
(
    SELECT MIN(calldate) AS MinCallDate, MAX(calldate) AS MaxCallDate
    FROM calls
)Sub0
CROSS JOIN
(
    SELECT units.i + tens.i * 10 + hundreds.i * 100 as i
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
) Sub1
INNER JOIN 
(
    SELECT calldate, ADDDATE(calldate, INTERVAL billsec SECOND) AS callenddate
    FROM calls
)   b
ON DATE_FORMAT(ADDDATE('2013-05-14 09:40:30', INTERVAL Sub1.i SECOND),"%Y%m%d%H%i%s") BETWEEN b.calldate AND b.callenddate
WHERE ADDDATE(MinCallDate, INTERVAL Sub1.i SECOND) <= MaxCallDate
AND Sub1.i < TIMESTAMPDIFF(SECOND,'2013-05-14 09:40:30', '2013-05-14 09:41:00')
GROUP BY aDate

如果您可以对您想要实现的目标提供非 SQL 解释，我也许能想出更好的方法。

编辑 - 对于简单的计数:-

SELECT Sub1.TimeSecond, COUNT(Sub2.CallSecond)
FROM
(
    SELECT ADDDATE('2013-05-14 09:40:30', INTERVAL units.i + tens.i * 10 + hundreds.i * 100 + thousands.i * 1000 SECOND) AS TimeSecond
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) thousands
    HAVING TimeSecond BETWEEN '2013-05-14 09:40:30' AND '2013-05-14 09:41:00'
) Sub1
LEFT OUTER JOIN
(
    SELECT ADDDATE(calldate, INTERVAL units.i + tens.i * 10 + hundreds.i * 100 + thousands.i * 1000 SECOND) AS CallSecond
    FROM calls
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) thousands
    WHERE units.i + tens.i * 10 + hundreds.i * 100 + thousands.i * 1000 <= billsec
) Sub2
ON Sub1.TimeSecond = Sub2.CallSecond
GROUP BY Sub1.TimeSecond

这将处理长达 9999 秒的范围/电话(很容易将其扩展到更大的范围，但这会使其变慢。但是不确定这些是否会有效，因为 MySQL 无法真正使用索引对于任何连接。

更简单的是这样，因为它不会在每次调用持续时间内每次生成:-

SELECT Sub1.TimeSecond, COUNT(calls.calldate)
FROM
(
    SELECT ADDDATE('2013-05-14 09:40:30', INTERVAL units.i + tens.i * 10 + hundreds.i * 100 + thousands.i * 1000 SECOND) AS TimeSecond
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) thousands
    HAVING TimeSecond BETWEEN '2013-05-14 09:40:30' AND '2013-05-14 09:41:00'
) Sub1
LEFT OUTER JOIN calls
ON Sub1.TimeSecond BETWEEN calls.calldate AND ADDDATE(calls.calldate, INTERVAL calls.billsec SECOND)
GROUP BY Sub1.TimeSecond

你能改变表格的布局吗？如果是这样，添加通话结束日期时间列可能会有所帮助。