mysql - SQL命令计算多列的结果？

Question

考虑下面标题为 'calls' 的 SQL 表，它是来自 John 电话账单的完整呼叫列表：

Sample Data Set for a Call log:
from, to, length (min)
John, Jill, 15
John, Jill, 7
Jill, John, 4
John, Jill, 6
John, Jill, 9

我发现以下查询：

SELECT calls.from, calls.to, COUNT(1) AS call_count
FROM `mydb.calls` AS calls
WHERE calls.from = "John" OR calls.to = "John"
GROUP BY calls.from, calls.to
ORDER by call_count DESC

给我以下结果：

row, from, to, call_count
1, John, Jill, 4
2, Jill, John, 1

我真正想要但我不知道的是如何从 John 的角度计算呼入和呼出的总结果。我想基本上显示以下内容：

row, who, inbound, outbound, total
1, Jill, 1, 4, 5

任何帮助将不胜感激！

标签： mysqlsql

Answer 1

您可以对分组键使用条件逻辑：

SELECT (CASE WHEN c.from = 'John' THEN c.to ELSE c.from END) as who, 
       COUNT(*) AS call_count
FROM `mydb.calls` c
WHERE 'John' in (c.from, c.to)
GROUP BY who
ORDER by call_count DESC;

SELECT (CASE WHEN c.from = 'John' THEN c.to ELSE c.from END) as who, 
       COUNT(*) AS call_count,
       SUM(c.from = 'John') as inbound,
       SUM(c.to = 'John') as outbound
FROM `mydb.calls` c
WHERE 'John' in (c.from, c.to)
GROUP BY who
ORDER by call_count DESC