一、分组查询并统计各分组数量
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
网友评论