php - mysql子查询计数位置和分组依据

Question

我有4张 table

cc_agents

+----+----------+
| id | username |
+----+----------+
|  1 | sankar   |
|  2 | jenifer  |
|  3 | andrew   |
|  4 | nirmal   |
|  5 | raja     |
+----+----------+

cc_callers

+----+-----------+
| id |   name    |
+----+-----------+
|  1 | sankar    |
|  2 | nirmal    |
|  3 | jenifer   |
|  4 | raja      |
|  5 | sankar    |
|  6 | office    |
|  7 | andrew    |
|  8 | sabarish  |
|  9 | saravanan |
+----+-----------+

cc_caller_requirement

+----+-------------+--------------+
| id | cc_agent_id | cc_caller_id |
+----+-------------+--------------+
|  1 |           1 |            5 |
|  2 |           1 |            5 |
|  3 |           1 |            2 |
|  4 |           1 |            2 |
|  5 |           1 |            7 |
|  6 |           4 |            2 |
| 14 |           1 |            2 |
| 13 |           5 |            2 |
| 12 |           5 |            2 |
| 15 |           1 |            8 |
| 16 |           1 |            9 |
+----+-------------+--------------+

cc_notifications

+----+-------------+--------------+-------------------+----------------------+
| id | cc_agent_id | cc_caller_id | cc_requirement_id | cc_notification_type |
+----+-------------+--------------+-------------------+----------------------+
| 1  | 1           | 5            | 1                 | sms                  |
| 2  | 1           | 5            | 1                 | mail                 |
| 3  | 1           | 5            | 1                 | courier              |
| 4  | 1           | 5            | 2                 | sms                  |
| 5  | 1           | 5            | 2                 | mail                 |
| 6  | 1           | 2            | 3                 | sms                  |
| 7  | 1           | 2            | 4                 | sms                  |
| 8  | 1           | 2            | 4                 | mail                 |
| 9  | 1           | 2            | 4                 | courier              |
| 10 | 1           | 7            | 5                 | mail                 |
| 11 | 1           | 7            | 5                 | courier              |
| 12 | 4           | 2            | 6                 | sms                  |
| 13 | 4           | 2            | 6                 | mail                 |
| 14 | 4           | 2            | 6                 | courier              |
| 30 | 5           | 2            | 12                | sms                  |
| 31 | 5           | 2            | 12                | mail                 |
| 32 | 5           | 2            | 12                | courier              |
| 33 | 5           | 2            | 13                | sms                  |
| 34 | 5           | 2            | 13                | mail                 |
| 35 | 5           | 2            | 13                | courier              |
| 36 | 1           | 2            | 14                | sms                  |
| 37 | 1           | 8            | 15                | sms                  |
| 38 | 1           | 8            | 15                | mail                 |
| 39 | 1           | 9            | 16                | sms                  |
| 40 | 1           | 9            | 16                | mail                 |
+----+-------------+--------------+-------------------+----------------------+

我执行的sql查询是

SELECT cca.id, cca.username,
   (SELECT COUNT(cccr.id) 
    FROM cc_caller_requirements AS cccr 
    WHERE cccr.cc_agent_id = cca.id 
    GROUP BY cccr.cc_caller_id) AS num_of_callers,
   (SELECT COUNT(ccns.id) 
    FROM cc_notifications AS ccns 
    WHERE ccns.cc_agent_id = cca.id  
    AND   ccns.cc_notification_type_id = 'sms') AS sms,
   (SELECT COUNT(ccnm.id) 
    FROM cc_notifications AS ccnm 
    WHERE ccnm.cc_agent_id = cca.id 
    AND   ccnm.cc_notification_type_id = 'mail') AS mail,
   (SELECT COUNT(ccna.id) 
    FROM cc_notifications AS ccna 
    WHERE ccna.cc_agent_id = cca.id 
    AND   ccna.cc_notification_type_id = 'courier') AS courier
FROM cc_agents AS cca
GROUP BY cca.id

我正在寻找这样的输出:

+------------+---------------+-----------+------------+---------------+
| agent name | no of callers | total sms | total mail | total courier |
+------------+---------------+-----------+------------+---------------+
| sankar     | 5             | 7         | 6          | 3             |
| jenifer    | 0             | 0         | 0          | 0             |
| andrew     | 0             | 0         | 0          | 0             |
| nirmal     | 1             | 1         | 1          | 1             |
| raja       | 1             | 2         | 2          | 2             |
+------------+---------------+-----------+------------+---------------+

代理名称、总短信、总邮件和总 express 数据运行良好...

但是当我不想要调用者时，我得到这个错误“子查询返回超过 1 行”

请帮我解决一下

Answer 1

将您的查询更改为:

SELECT cca.id, cca.username,
   (SELECT COUNT(DISTINCT cccr.cc_caller_id) 
    FROM cc_caller_requirements AS cccr 
    WHERE cccr.cc_agent_id = cca.id 
    ) AS num_of_callers,
   (SELECT COUNT(ccns.id) 
    FROM cc_notifications AS ccns 
    WHERE ccns.cc_agent_id = cca.id  
    AND   ccns.cc_notification_type_id = 'sms') AS sms,
   (SELECT COUNT(ccnm.id) 
    FROM cc_notifications AS ccnm 
    WHERE ccnm.cc_agent_id = cca.id 
    AND   ccnm.cc_notification_type_id = 'mail') AS mail,
   (SELECT COUNT(ccna.id) 
    FROM cc_notifications AS ccna 
    WHERE ccna.cc_agent_id = cca.id 
    AND   ccna.cc_notification_type_id = 'courier') AS courier
FROM cc_agents AS cca
GROUP BY cca.id