将MySQL查询从WHERE更改为LEFT JOIN

Question

我有一个查询，它报告Asterisk调用的使用情况和队列统计信息。查询当前使用MySQL WHERE子句联接表并过滤数据。

这就是目前查询的样子：

SELECT 
    c.name as 'Agent',
    e.extended_number AS 'Extension',
    COUNT(ql.`time`) AS 'Total Inbound Calls',
    (SELECT 
            COUNT(1) 
    FROM
        call_history ch,
        ast_queue_mstatus qm
    WHERE
        ch.start >= (DATE_SUB(DATE(NOW()),
            INTERVAL 10050 MINUTE))
            AND ch.start <= (DATE(NOW()))
            AND ch.calltype = 'out'
            AND ch.flow = 'out'
            AND ch.extension_number = qm.membername
            AND qm.membername = e.extended_number
    GROUP BY qm.membername) AS 'Total Outbound Calls',
    FORMAT(SUM(CAST(ql.arg2 AS UNSIGNED)) / 60, 2) AS 'Total Inbound Duration',
    FORMAT(AVG(CAST(ql.arg2 AS UNSIGNED)) / 60, 2) AS 'Avg Inbound Duration',
    FORMAT(AVG(CAST(ql.arg1 AS UNSIGNED)) / 60, 2) AS 'Avg Caller Hold Time'
FROM
    ast_queue_log ql,
    ast_queue_mstatus qs,
    client c,
    extension e
WHERE
    ql.queuename = '1234'
    AND ql.`time` >= (DATE_SUB(DATE(NOW()), INTERVAL 7 DAY))
    AND ql.`time` <= (DATE(NOW()))
    AND (ql.event = 'COMPLETEAGENT'
    OR ql.event = 'COMPLETECALLER'
    OR ql.event = 'COMPLETETRANSFER')
    AND RIGHT(ql.agent, 8) = qs.membername
    AND qs.membername = e.extended_number
    AND e.client_id = c.id      
GROUP BY ql.agent
ORDER BY c.name;

我遇到的问题是，我需要返回未收到任何入站呼叫的队列成员的出站呼叫结果。目前，如果ast_queue_log中没有记录，那么WHERE子句将排除这些记录，这意味着标量子查询不会拾取这些记录。

如何更改此查询，以便从call_history获取所有呼叫记录，其中ast_queue_mstatus中有相应的记录？

很抱歉，如果这太复杂或没有足够的信息，如果这没有意义，我可以尝试简化！

Answer 1

是时候抛弃那些旧的逗号分隔的表列表，使用1992年引入的联接标准了（是的）。：-）

因为那样你就可以使用LEFT JOIN了。对于这种联接，关系是可选的。也就是说，返回第一个表中的所有行（如果它们与WHERE子句中的条件匹配），但联接的表不必也有记录。如果没有，则仍会返回一行，但字段中会填充NULL值。

SELECT 
    c.name as 'Agent',
    e.extended_number AS 'Extension',
    COUNT(ql.`time`) AS 'Total Inbound Calls',
    (SELECT 
        COUNT(1) 
    FROM
        call_history ch
        INNER JOIN ast_queue_mstatus qm 
          ON qm.membername = ch.extension_number
    WHERE
        ch.start >= (DATE_SUB(DATE(NOW()), INTERVAL 10050 MINUTE))
        AND ch.start <= (DATE(NOW()))
        AND ch.calltype = 'out'
        AND ch.flow = 'out'
        AND ch.extension_number = e.extended_number
    GROUP BY 
       ch.extension_number) AS 'Total Outbound Calls',
    FORMAT(SUM(CAST(ql.arg2 AS UNSIGNED)) / 60, 2) AS 'Total Inbound Duration',
    FORMAT(AVG(CAST(ql.arg2 AS UNSIGNED)) / 60, 2) AS 'Avg Inbound Duration',
    FORMAT(AVG(CAST(ql.arg1 AS UNSIGNED)) / 60, 2) AS 'Avg Caller Hold Time'
FROM
    client c,
    INNER JOIN extension e 
        ON e.client_id = c.id
    INNER JOIN ast_queue_mstatus qs 
        ON qs.membername = e.extended_number
    LEFT JOIN ast_queue_log ql 
        ON ql.queuename = '1234'
        AND ql.`time` >= (DATE_SUB(DATE(NOW()), INTERVAL 7 DAY))
        AND ql.`time` <= (DATE(NOW()))
        AND (
            ql.event = 'COMPLETEAGENT'
            OR ql.event = 'COMPLETECALLER'
            OR ql.event = 'COMPLETETRANSFER')
        AND RIGHT(ql.agent, 8) = qs.membername
GROUP BY ql.agent
ORDER BY c.name;

您也可以使用RIGHT JOIN以其他方式加入（第一个表是可选的）。但总的来说，这很难解释。