mysql - 将相关的mysql子查询转换为不相关的

我需要帮助将相关的子查询转换为不相关的查询，这是一个查询：

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

谢谢你的建议

最佳答案

我真的很努力地弄清楚您的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

您可以更改表格的布局吗？如果是这样，为呼叫结束日期时间添加一列可能会有所帮助。

关于mysql - 将相关的mysql子查询转换为不相关的，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/17522947/