一段值得纪念的SQL语句

select m.dfhm,m.zbjlx,count(1),round(sum(m.thsc)/count(1),1)
  from T_ANALYS_PHONE_DETAIL m
 where m.dfhm in (select dfhm
                  from (select t.dfhm, t.zbjlx
                          from T_ANALYS_PHONE_DETAIL t
                         where pid = 'PM460100140000201111000001'
                         group by t.dfhm, t.zbjlx)
                 group by dfhm
                having count(1) < 2)
    and pid = 'PM460100140000201111000001'
                  group by m.dfhm,m.zbjlx
                having count(1)>=30

数据源参考

pid	dfhm(对方号码)	zbjlx(主被呼叫类型)	thsc（通话时长）	......
PM460100140000201111000001	12345678	主叫	32
PM460100140000201111000001	22222222	被叫	123
PM460100140000201111000001	12345678	主叫	132
PM460100140000201111000002	22222222	主叫	234
PM460100140000201111000001	22222222	被叫	52
PM460100140000201111000001	87654321	主叫	57
PM460100140000201111000001	68787987	主叫	5
...	...	...	...

 

 

查询结果参考

对方号码（dfhm）	类型	次数（>30）	平均时长（秒）	选择
12345678	只呼不应	60	35.5
22222222	只应不呼	40	23
87654321	只呼不应	35	222.4
...	...	...	...