mysql 多表关联查询

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;



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值