mysql 多表关联查询 统计数据
SELECT t.ID AS articleId,
t.title AS title,
t.SPECIAL_ID AS specialId,
t.CREATOR AS createId,
t.author AS authorId,
t.FACT_TIME AS factTime,
IFNULL(tv.numStr,0) AS viewCount,
IFNULL(ts.numStr,0) AS shareCount,
IFNULL(tl.numStr,0) AS likesCount,
IFNULL(tc.numStr,0) AS collectCount
FROM
(SELECT ID,
TITLE,
SPECIAL_ID,
CREATOR,
AUTHOR,
FACT_TIME
FROM t_article
WHERE 1=1
AND SPECIAL_ID='e548fdc054f188670154f1b6366c000b'
AND CREATOR='e548fdc054f170770154f17ffe12000c'
AND TITLE LIKE '%仅仅是个开始”%'
ORDER BY FACT_TIME DESC) AS t
LEFT JOIN
(SELECT ARTICLE_ID,
CREATE_TIME,
count(ID) AS numStr
FROM t_article_view_record
WHERE 1=1
AND CREATE_TIME>='2016-06-01'
AND CREATE_TIME<='2016-07-22'
GROUP BY ARTICLE_ID) AS tv ON t.ID = tv.ARTICLE_ID
LEFT JOIN
(SELECT ARTICLE_ID,
CREATE_TIME,
count(ID) AS numStr
FROM t_article_share_record
WHERE 1=1
AND CREATE_TIME>='2016-06-01'
AND CREATE_TIME<='2016-07-22'
GROUP BY ARTICLE_ID) AS ts ON t.ID = ts.ARTICLE_ID
LEFT JOIN
(SELECT ARTICLE_ID,
CREATE_TIME,
count(ID) AS numStr
FROM t_article_likes_record
WHERE 1=1
AND CREATE_TIME>='2016-06-01'
AND CREATE_TIME<='2016-07-22'
GROUP BY ARTICLE_ID) AS tl ON t.ID = tl.ARTICLE_ID
LEFT JOIN
(SELECT ARTICLE_ID,
CREATE_TIME,
count(ID) AS numStr
FROM t_article_collection_record
WHERE 1=1
AND CREATE_TIME>='2016-06-01'
AND CREATE_TIME<='2016-07-22'
GROUP BY ARTICLE_ID) AS tc ON t.ID = tc.ARTICLE_ID
WHERE 1=1
AND tv.numStr>=9
AND tv.numStr<=100000
ORDER BY viewCount DESC LIMIT 0, 20;