分组统计查询和存储过程

一、分组查询并统计各分组数量

SELECT *,COUNT(*) as be_liked_num FROM

(SELECT c.*,v.uid as author_id

FROM ypt_collect_object c

LEFT JOIN ypt_short_video v ON c.collect_id = v.id

WHERE c.type = 2 and c.collect_type = 5

ORDER BY c.id desc) as collect_info

GROUP BY collect_info.collect_id;

二、合并查询结果

（SELECT *,COUNT(*) as be_liked_num FROM

(SELECT c.*,v.uid as author_id

FROM ypt_collect_object c

LEFT JOIN ypt_short_video v ON c.collect_id = v.id

WHERE c.type = 2 and c.collect_type = 5

ORDER BY c.id desc) as collect_info

GROUP BY collect_info.collect_id)

UNION

(SELECT *,COUNT(*) as be_liked_num FROM

(SELECT c.*,v.user_id as author_id

FROM ypt_collect_object c

LEFT JOIN ypt_comments v ON c.collect_id = v.id

WHERE c.type = 2 and c.collect_type = 8

ORDER BY c.id desc) as collect_info

GROUP BY collect_info.collect_id)

ORDER BY addtime desc）

三、存储过程

BEGIN

#Routine body goes here...

# 定义变量

  DECLARE begin_num INT(11);

SET begin_num = page * page_size;

SELECT * FROM (

(SELECT *,COUNT(*) as be_liked_num FROM

(SELECT c.*,v.uid as author_id

FROM ypt_collect_object c

LEFT JOIN ypt_short_video v ON c.collect_id = v.id

WHERE c.type = 2 and c.collect_type = 5

ORDER BY c.id desc) as collect_info

GROUP BY collect_info.collect_id)

UNION

(SELECT *,COUNT(*) as be_liked_num FROM

(SELECT c.*,v.user_id as author_id

FROM ypt_collect_object c

LEFT JOIN ypt_comments v ON c.collect_id = v.id

WHERE c.type = 2 and c.collect_type = 8

ORDER BY c.id desc) as collect_info

GROUP BY collect_info.collect_id)

ORDER BY addtime desc

) as info WHERE info.author_id = user_id

LIMIT begin_num,page_size;

END