SELECT

 Baoming.id,

  Baoming.addtime AS '报名时间',

  CONCAT(Members.realname,Members.username) AS '客服姓名',

  Sem.name  AS `渠道来源`,

  CASE WHEN bmcs_fd_time IS NOT NULL THEN "分单" ELSE "未分单" END AS `是否分单`,

  CASE WHEN (SELECT COUNT(id) FROM come_go_phone_logs WHERE baoming_id = Baoming.id AND connect_status = 1  AND  call_mode = 2 ) > 0 THEN "接通" ELSE "未接通" END AS `是否接通`,

  (SELECT COUNT(id) FROM come_go_phone_logs WHERE baoming_id = Baoming.id   AND  call_mode = 2 ) AS '联系次数',

  (SELECT MIN(call_time) FROM come_go_phone_logs WHERE baoming_id = Baoming.id  AND  call_mode = 2 ) AS '第一次联系时间',

  (SELECT MIN(call_time) FROM come_go_phone_logs WHERE baoming_id = Baoming.id  AND  call_mode = 2  AND call_time>`第一次联系时间`) AS '第2次联系时间',

  (SELECT MIN(call_time) FROM come_go_phone_logs WHERE baoming_id = Baoming.id   AND  call_mode = 2  AND call_time>`第2次联系时间`) AS '第3次联系时间',

  (SELECT MIN(call_time) FROM come_go_phone_logs WHERE baoming_id = Baoming.id   AND  call_mode = 2  AND call_time>`第3次联系时间`) AS '第4次联系时间'

FROM

  `baomings` AS Baoming

 LEFT JOIN come_go_phone_logs AS Phone

 ON Phone.baoming_id = Baoming.id

 LEFT JOIN `members` AS Members

 ON Phone.seats_cs= Members.`id`

 LEFT JOIN `sem_sources` AS Sem

 ON Sem.j_name=Baoming.bm_laiyuan

 WHERE Baoming.addtime >= '2017-07-01 00:00:00'

  AND Baoming.is_fp = 1

  AND Baoming.zx_tuijian = 1

  AND Baoming.addtime <= '2017-07-11 23:59:59'

  AND Baoming.id IN ( SELECT  baoming_id ,COUNT(id) AS c FROM come_go_phone_logs WHERE  connect_status = 1  AND  call_mode = 2  )

  GROUP BY Baoming.id