mysql - 搜索查询条件列是否可能？

Question

我有一个根据 call_type 记录了持续时间(秒)的表。现在我想在一行中显示调用类型的传入和传出及其持续时间。

调用表

|ID | originating call |   terminating call |  call type    | duration_seconds
| 1 | 123              |       123          |  incoming     |     60
| 2 | 123              |       123          |  outgoing     |     120 
| 3 | 123              |       321          |  incoming     |     210
| 4 | 123              |       321          |  incoming     |     140

结果将是

|ID | originating call |   terminating call |  incoming  |   duration | outgoing   | duration 
| 1 | 123              |       123          |     1      |     60     |      1     |   120
| 2 | 123              |       321          |     2      |     350    |      0     |   0

这是我到目前为止的查询。

select @id := @id + 1 as id,
   originating, terminating,
   sum(calltype = 'incoming') as incoming,
   sum(calltype = 'outgoing') as outgoing,
from calltable ct cross join
 (select @id := 0) const
group by originating, terminating;


 originating call |   terminating call |  incoming  | outgoing
      123         |       123          |     1      |    1
      123         |       321          |     2      |    0

Answer 1

只需根据调用类型添加另外 2 列，就像您对传入和传出所做的那样，只是不计数 1，而是使用 duration_seconds

select row_number() over (order by (select NULL)) as id,
   originating, terminating,
   sum(case when calltype = 'incoming' then 1 else 0 end) as incoming,
   sum(case when calltype = 'incoming' then duration_seconds else 0 end) as in_duration,
   sum(case when calltype = 'outgoing' then 1 else 0 end) as outgoing,
   sum(case when calltype = 'outgoing' then duration_seconds else 0 end) as out_duration
from calltable ct
group by originating, terminating;

这是根据您更新的问题的 mysql。

select @id := @id + 1 as id,
   originating, terminating,
   sum(calltype = 'incoming') as incoming,
   sum(if(calltype = 'incoming',duration_seconds,0)) as in_duration,
   sum(calltype = 'outgoing') as outgoing,
   sum(if(calltype = 'outgoing',duration_seconds,0)) as out_duration
from calltable ct cross join
 (select @id := 0) const
group by originating, terminating;