sql

SELECT tt.visitor_id, tt.visitor_name, tt.marking_time,tt.job_id, tt.last_service_job_id, sessions,
COUNT(*),
SUM(IF (t2.end_time >= NOW() AND t2.start_time <= NOW() AND t2.operate_time > tt.marking_time, 1, 0)) allAfterServ,
SUM(IF (t2.end_time >= NOW() AND t2.start_time <= NOW() AND t2.operate_time > tt.marking_time AND t2.insured_identity = 1, 1, 0)) forSelf,
SUM(IF (t2.end_time >= NOW() AND t2.start_time <= NOW() AND t2.operate_time > tt.marking_time AND t2.insured_identity IN (2, 3, 4), 1, 0)) forFamily
FROM (
SELECT * FROM interested_visitor t
WHERE EXISTS (SELECT id FROM bd_visitor_info t1 WHERE t1.visitor_id = t.visitor_id AND t1.end_time >= NOW() AND t1.start_time <= NOW() AND t1.operate_time > t.marking_time)
ORDER BY t.marking_time DESC
LIMIT 0, 15
) tt, bd_visitor_info t2
WHERE tt.visitor_id = t2.visitor_id
GROUP BY tt.visitor_id, tt.visitor_name, tt.marking_time,tt.job_id, tt.last_service_job_id, sessions

 

EXPLAIN
SELECT tt.visitor_id, tt.visitor_name, tt.marking_time,tt.nickname, u2.nickname, sessions,
COUNT(*),
SUM(IF (t2.end_time >= NOW() AND t2.start_time <= NOW() AND t2.operate_time > tt.marking_time, 1, 0)) allAfterServ,
SUM(IF (t2.end_time >= NOW() AND t2.start_time <= NOW() AND t2.operate_time > tt.marking_time AND t2.insured_identity = 1, 1, 0)) forSelf,
SUM(IF (t2.end_time >= NOW() AND t2.start_time <= NOW() AND t2.operate_time > tt.marking_time AND t2.insured_identity IN (2, 3, 4), 1, 0)) forFamily
FROM (
SELECT t.*, u1.nickname FROM interested_visitor t LEFT JOIN USER u1 ON t.job_id = u1.jobId
WHERE u1.nickname LIKE '%钟%'

AND EXISTS (SELECT id FROM bd_visitor_info t1 WHERE t1.visitor_id = t.visitor_id AND t1.end_time >= NOW() AND t1.start_time <= NOW() AND t1.operate_time > t.marking_time)
ORDER BY t.marking_time DESC
-- limit 0, 15
) tt, bd_visitor_info t2, USER u2
WHERE tt.visitor_id = t2.visitor_id AND tt.job_id=u2.jobId
GROUP BY tt.visitor_id, tt.visitor_name, tt.marking_time,tt.nickname, u2.nickname, sessions

转载于:https://www.cnblogs.com/zhongchang/p/9058488.html