多行的统计数据汇总为单行

-- 文章表、用户行为表（文章Id-Uid-行为类型）

SELECT t0.Id,t1.n AS FavouriteNum, t2.n AS LikeNum FROM article AS t0
LEFT JOIN ( SELECT COUNT(1) AS n,ArticleId FROM article_user_behavior WHERE BehaviorType=1 GROUP BY ArticleId) AS t1
ON t0.Id=t1.ArticleId
LEFT JOIN ( SELECT COUNT(1) AS n,ArticleId FROM article_user_behavior WHERE BehaviorType=2 GROUP BY ArticleId ) AS t2
ON t0.Id=t2.ArticleId;

-- 多行的统计数据汇总为单行

注意：JOIN时的ON连接条件

SELECT Id FROM (

SELECT t0.Id,IF(t1.n IS NULL,0,t1.n) AS FavouriteNum,IF(t2.n IS NULL,0,t2.n) AS LikeNum FROM article_content AS t0
LEFT JOIN ( SELECT COUNT(1) AS n,ArticleId FROM article_user_behavior WHERE BehaviorType=1 ) AS t1
ON t0.Id=t1.ArticleId
LEFT JOIN ( SELECT COUNT(1) AS n,ArticleId FROM article_user_behavior WHERE BehaviorType=2 ) AS t2
ON t0.Id=t2.ArticleId

) AS ta ORDER BY FavouriteNum*10+LikeNum*20 DESC LIMIT 4

多行的统计数据汇总为单行

原文：https://www.cnblogs.com/rsapaper/p/14738617.html