主页 / dba / 问题 / 106060
Accepted
Bikram Pahi
Asked: 2015-07-07 05:04:59 +0800 CST

在 MySQL 子查询上需要一些帮助

  • 772

我在 MySQL 中有一张表。

表结构如下。

id   |  emp_id  |  call_start  |  call_end  |  call_type
--------------------------------------------------------
1    | 20043    | 20:20:10     | 20:21:19   | SALES
2    | 20043    | 20:25:34     | 20:27:12   | NON-SALES
3    | 20044    | 21:25:34     | 21:27:12   | SALES
4    | 20044    | 22:25:34     | 22:27:12   | SALES
5    | 20044    | 22:45:34     | 22:47:12   | NON-SALES
6    | 20043    | 21:25:34     | 21:27:12   | NON-SALES

我想选择数据作为员工的通话总数，员工的总销售电话。

什么是合适的查询？

我写了一些东西但结果不正确：

SELECT 
  emp_id, 
  count(*) total, 
  (SELECT COUNT(*) AS TOTAL_CALL FROM Dash_Agent_Disp) total_calls,call_type 
FROM 
  `Dash_Agent_Disp` 
where 
  call_type like 'SALES' 
GROUP BY 
  employee_id

查询的输出是：

emp_id  |  total  | total_calls  |  call_type
----------------------------------------------
20043   |   1     |   6          |  SALES
20044   |   2     |   6          |  SALES

我的期望是得到：

emp_id  |  total  | total_calls  |  call_type
----------------------------------------------
20043   |   1     |   3          |  SALES
20044   |   2     |   3          |  SALES
mysql subquery

2 个回答

  1. 你需要过滤你的子查询call_type='SALES'，然后运行：

    我使用你的信息：

    INSERT INTO test.Dash_Agent_Disp
    (id,emp_id,call_start,call_end,call_type)
VALUES
    ('1','20043','20:20:10','20:21:19','SALES'),
    ('2','20043','20:25:34','20:27:12','NON-SALES'),
    ('3','20044','21:25:34','21:27:12','SALES'),
    ('4','20044','22:25:34','22:27:12','SALES'),
    ('5','20044','22:45:34','22:47:12','NON-SALES'),
    ('6','20043','21:25:34','21:27:12','NON-SALES');

    询问：

    SELECT 
    d.emp_id, 
    count(d.id) total, 
    (SELECT COUNT(*) FROM Dash_Agent_Disp WHERE call_type='SALES') as total_calls,
    d.call_type
FROM Dash_Agent_Disp as d
WHERE d.call_type='SALES'
GROUP BY 
    d.emp_id,
    d.call_type;

    结果：

    mysql> SELECT  d.emp_id,  count(d.id) total,  (SELECT COUNT(*) FROM Dash_Agent_Disp WHERE call_type='SALES') as total_calls, d.call_type FROM Dash_Agent_Disp as d WHERE d.call_type='SALES' GROUP BY  d.emp_id, d.call_type;
+--------+-------+-------------+-----------+
| emp_id | total | total_calls | call_type |
+--------+-------+-------------+-----------+
|  20043 |     1 |           3 | SALES     |
|  20044 |     2 |           3 | SALES     |
+--------+-------+-------------+-----------+
2 rows in set (0.00 sec)

mysql>
    • 3
  2. Best Answer

    感谢您的所有努力，我这样做了。

    select a.emp_id, a.total, b.TOTAL_CALLS 
from (SELECT a.emp_id,count(*) total FROM `Dash_Agent_Disp` a where a.call_type = 'sales' GROUP BY a.emp_id) a, 
(select emp_id, count(*) total_calls from `Dash_Agent_Disp` group by emp_id) b
WHERE a.emp_id=b.emp_id

    谢谢大家。

    • 2

相关问题