mysql - 从一个输出中的两个不同表查询每个用户的调用统计信息

Question

SELECT c.id_client, d.login, COUNT(*) AS calls
FROM voip.calls c, api.clients d WHERE c.id_client=d.id_client
AND c.call_start>='2013-10-28 00:00:00' AND c.call_start<'2013-11-11 00:00:00'
AND d.id_client IN 
(SELECT  e.idclient FROM voip.invoiceclients e WHERE e.clientnr='demo')
GROUP BY id_client;

计算每个用户 (id_client) 的调用:

id_client、登录、通话
369、10315、20
373、10319、3
389、10335、9
517, 10460, 1
519、10462、2
520、10463、2
540、10483、2

<小时/>

SELECT c.id_client, d.login, COUNT(*) AS calls_failed
FROM voip.callsfailed c, api.clients d WHERE c.id_client=d.id_client
AND c.call_start>='2013-10-28 00:00:00' AND c.call_start<'2013-11-11 00:00:00'
AND c.`IE_error_number`<>0
AND d.`id_client` IN
(SELECT  e.idclient FROM voip.invoiceclients e WHERE e.clientnr='demo')
GROUP BY id_client;

计算每个用户 (id_client) 的失败调用:

id_client、登录、fail_calls
369、1000315、11
373、1000319、2
389、1000335、9
458, 1000404, 1
517, 1000460, 1
519, 1000462, 1
566、1000509、3
591、1000534、13

<小时/>

如何编写查询以在同一输出中提供调用和失败的调用:

id_client、登录、通话、fail_calls
369、10315、20、11
373, 10319, 3, 2
389, 10335, 9, 9
517, 10460, 1, 1
519, 10462, 2, 1
520, 10463, 2, 0
540, 10483, 2, 0
566, 0, 3
591、10534、2,13

Answer 1

两种方式:

1. UNION您从现有查询中获得的内容并对结果进行SUM:

   SELECT id_client, login, 
       SUM(calls) AS calls, SUM(calls_failed) AS calls_failed
   FROM
   (
       SELECT c.id_client, d.login, COUNT(*) AS calls, 0 AS calls_failed
       FROM voip.calls c, api.clients d WHERE c.id_client=d.id_client
       AND c.call_start>='2013-10-28 00:00:00' AND c.call_start<'2013-11-11 00:00:00'
       AND d.id_client IN 
       (SELECT  e.idclient FROM voip.invoiceclients e WHERE e.clientnr='demo')
       GROUP BY id_client
       UNION ALL
       SELECT c.id_client, d.login, 0 AS calls, COUNT(*) AS calls_failed
       FROM voip.callsfailed c, api.clients d WHERE c.id_client=d.id_client
       AND c.call_start>='2013-10-28 00:00:00' AND c.call_start<'2013-11-11 00:00:00'
       AND c.`IE_error_number`<>0
       AND d.`id_client` IN
       (SELECT  e.idclient FROM voip.invoiceclients e WHERE e.clientnr='demo')
       GROUP BY id_client
   ) x
   GROUP BY id_client, login
   

2. 清理您的查询，找出公共(public)部分，您将得到:

   SELECT d.id_client, d.login, 
       COALESCE(c.total, 0) AS calls, COALESCE(fc.total, 0) AS calls_failed
   FROM api.clients d
   LEFT OUTER JOIN
   (
       SELECT COUNT(*) AS total, id_client
       FROM voip.calls c
       WHERE c.call_start >= '2013-10-28 00:00:00' 
       AND c.call_start < '2013-11-11 00:00:00'
       GROUP BY id_client
   ) c ON d.id_client = c.id_client
   LEFT OUTER JOIN
   (
       SELECT COUNT(*) AS total, id_client
       FROM voip.callsfailed c
       WHERE c.call_start >= '2013-10-28 00:00:00' 
       AND c.call_start < '2013-11-11 00:00:00'
       AND c.IE_error_number <> 0
       GROUP BY id_client
   ) fc ON d.id_client = fc.id_client   
   WHERE d.id_client IN
   (
       SELECT e.idclient
       FROM voip.invoiceclients e
       WHERE e.clientnr='demo'
   )