SELECT * FROM
(
SELECT threads._id, threads_tmp._id AS smsid, threads_tmp.type, threads.read, threads_tmp.msgtype, threads_tmp.status, threads.has_attachment, threads_tmp.msg_read, threads.date, threads.recipient_ids, threads.snippet
FROM threads left JOIN
(
SELECT thread_id, type, _id, read AS msg_read, msgtype, status
FROM
(
SELECT thread_id AS tid, date* 1000 AS normalized_date, msg_box AS type, date, _id, read, thread_id, 2 AS msgtype, NULL AS status
FROM pdu
WHERE ((msg_box != 3 AND (m_type = 128 OR m_type = 132 OR m_type = 130)))
GROUP BY thread_id HAVING date = MAX(date)
UNION
SELECT thread_id AS tid, date * 1 AS normalized_date, type, date, _id, read, thread_id, 1 AS msgtype, status
FROM sms
WHERE (sms.type != 3) GROUP BY thread_id HAVING date = MAX(date)
)
GROUP BY tid HAVING normalized_date = MAX(normalized_date)
) threads_tmp
ON threads._id = threads_tmp.thread_id
)
WHERE smsid <> 0 ORDER BY date DESC--;
------解决方案--------------------
- SQL code
---如果数据不是实时的话插入临时表#tempSELECT * #temp FROM( SELECT threads._id, threads_tmp._id AS smsid, threads_tmp.type, threads.read, threads_tmp.msgtype, threads_tmp.status, threads.has_attachment, threads_tmp.msg_read, threads.date, threads.recipient_ids, threads.snippet FROM threads left JOIN (SELECT thread_id, type, _id, read AS msg_read, msgtype, status FROM(SELECT thread_id AS tid, date* 1000 AS normalized_date, msg_box AS type, date, _id, read, thread_id, 2 AS msgtype, NULL AS status FROM pdu WHERE ((msg_box != 3 AND (m_type = 128 OR m_type = 132 OR m_type = 130))) GROUP BY thread_id HAVING date = MAX(date) UNIONSELECT thread_id AS tid, date * 1 AS normalized_date, type, date, _id, read, thread_id, 1 AS msgtype, status FROM sms WHERE (sms.type != 3) GROUP BY thread_id HAVING date = MAX(date)) GROUP BY tid HAVING normalized_date = MAX(normalized_date)) threads_tmp ON threads._id = threads_tmp.thread_id) WHERE smsid <> 0 ORDER BY date DESC
------解决方案--------------------
几点意见
1，GROUP BY thread_id HAVING date = MAX(date)
这样提取的数据有可能会有多条
2，要优化SQL，就不要用GROUP BY
3，连接用UNION 会过滤掉重复的数据，这样效率不是很高
4，建立合适的索引