mysql - 显示连接结果的多选查询

Question

来自表 'phone_call'

SELECT * FROM phone_call;

*************************** 181. row ***************************
     id: 682
user_id: 24
  start: 2013-09-03 17:59:18
    end: 2013-09-03 17:59:20
ext_num: 2008
destination: 123473654172
call_id: 5eab044f19ed41d70661c3220f1b5c65
direction: I
created_at: 2013-09-03 17:59:18
updated_at: 2013-09-03 17:59:20
 totals: 1
*************************** 182. row ***************************
     id: 683
user_id: 22
  start: 2013-09-03 17:59:18
    end: 2013-09-03 17:59:20
ext_num: 2002
destination: 123456587458
call_id: 2eb1d9ea2e1937be30f1e93878eb3efe
direction: O
created_at: 2013-09-03 17:59:18
updated_at: 2013-09-03 17:59:20
 totals: 1
182 rows in set (0.00 sec)

...more

根据上面的数据，我需要一个返回的查询:

 ext_num, username, calls_in, calls_out, avg_in, avg_out 

从数据来看，一切都在进行中，但有点挣扎。

我可以像这样得到各个部分:

SELECT AVG(timediff(end, start)) AS avg_in FROM phone_call WHERE user_id=24 AND  direction='I';
+---------------------------+
| avg_in                    |
+---------------------------+
| 22.0810810811             |
+---------------------------+
1 row in set (0.00 sec)

SELECT AVG(timediff(end, start)) AS avg_out FROM phone_call WHERE user_id=24 AND direction='O';
+---------+
| avg_out |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

此用户 (24) 没有“调用”(direction='O')。

和

SELECT ext_num, sum(totals) AS `calls_in`, u.username
FROM phone_call pc 
JOIN user u ON pc.user_id=u.id 
WHERE user_id=24 and direction='I';
+---------+----------+----------+
| ext_num | calls_in | username |
+---------+----------+----------+
|    2008 |       37 | Esi      |
+---------+----------+----------+
1 row in set (0.00 sec)

和

SELECT sum(totals) AS `calls_out`
FROM phone_call
WHERE user_id=24 and direction='O';
+-----------+
| calls_out |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

但是我究竟要如何将它们连接在一起以形成一个字符串或元组呢？

编辑:我的表结构:

describe phone_call;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id     | int(11)      | NO   | MUL | NULL    |                |
| start       | datetime     | YES  |     | NULL    |                |
| end         | datetime     | YES  |     | NULL    |                |
| ext_num     | int(11)      | YES  |     | NULL    |                |
| destination | varchar(45)  | YES  |     | NULL    |                |
| call_id     | varchar(100) | YES  |     | NULL    |                |
| direction   | varchar(1)   | YES  |     | NULL    |                |
| created_at  | datetime     | YES  |     | NULL    |                |
| updated_at  | datetime     | YES  |     | NULL    |                |
| totals      | int(1)       | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

编辑:@Mahmoud运行:

SELECT 
u.ext_num,
u.username,
AVG(CASE WHEN pc.direction = 'I' THEN timediff(pc.end, pc.start))  ELSE 0 END)AS avg_in,
AVG(CASE WHEN pc.direction = 'O' THEN timediff(pc.end, pc.start))  ELSE 0 END)AS avg_out,
sum(CASE WHEN pc.direction = 'I' THEN totals ELSE 0 END) AS `calls_in`, 
sum(CASE WHEN pc.direction = 'O' THEN totals ELSE 0 END) AS `calls_out`
FROM phone_call AS pc
JOIN user u ON pc.user_id=u.id 
WHERE u.user_id=24 
GROUP BY u.user_id, u.ext_num;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that     corresponds to your MySQL server version for the right syntax to use near ')  ELSE 0 END)AS avg_in,
AVG(CASE WHEN pc.direction = 'O' THEN timediff(pc.end' at line 4

Answer 1

您需要使用 CASE 表达式在一个查询中执行此操作，并使用条件 WHEN pc.direction = 'I' 和 when pc.每列的方向 = 'O':

SELECT 
  u.ext_num,
  u.username,
  AVG(CASE WHEN pc.direction = 'I' THEN timediff(pc.end, pc.start) END)AS avg_in,
  AVG(CASE WHEN pc.direction = 'O' THEN timediff(pc.end, pc.start) END)AS avg_out,
  sum(CASE WHEN pc.direction = 'I' THEN totals ELSE 0 END) AS `calls_in`, 
  sum(CASE WHEN pc.direction = 'O' THEN totals ELSE 0 END) AS `calls_out`
FROM phone_call AS pc
JOIN user u ON pc.user_id=u.id 
WHERE u.user_id=24 
GROUP BY u.user_id, u.ext_num;