mysql - 如何编写查询以找出我想要的结果

Question

sl  accountcode  billsec    port  disposition   linkedid           dcontext
1   9763719771    0         208    ANSWERED     1563550244.51394    from_a
2   9763719771    22               ANSWERED     1563550244.51394    a2
3   4739619421    0         201    ANSWERED     1563550304.51402    from_a
4   4739619421    21               ANSWERED     1563550304.51402    a2
5   4020885986    0         714    ANSWERED     1563550991.51453    from_a
6   4020885986    39               ANSWERED     1563550991.51453    a2
7   8743098677    0         208    NO ANSWER    1563550501.51417    from_a
8   8743098677    0                NO ANSWER    1563550501.51417    a2
9   9763719771    0         201    ANSWERED     1563551117.51476    from_a
10  9763719771    61               ANSWERED     1563551117.51476    a2

此表是我的 sip 服务器的呼叫详细信息记录。每次调用都会在数据库中创建 2 行，并且每次调用的linkedid 都相同。

我想找出端口 = 201 和linkedid 相等的 sum(billsec)。结果应该类似于端口 201 的 sum(billsec) 应该是 (22+61)=82

现在请帮我写查询。

标签： mysql

Answer 1

首先获取linkedid（子查询s）的最大值，然后求和其中端口= 201

select sum(maxbillsec) billsec
from
(
select linkedid,max(billsec) maxbillsec,max(port) maxport
from t 
group  by linkedid
) s 
where maxport = 201
; 

+---------+
| billsec |
+---------+
|      82 |
+---------+
1 row in set (0.00 sec)