sql 求max和min，但是第二大，第二小怎么算？

时间：2021-10-19 00:44:37

利用子查询，但这样速度较慢。

 

 

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

 

标签：

相关文章