mysql分组问题

积木工具箱

表结构如下

//用户动态表
dynamic_id 动态id
dynamic_con 动态内容
CREATE TABLE `dynamic` (
  `dynamic_id` int(255) DEFAULT NULL,
  `dynamic_con` varchar(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ----------------------------
-- Records of dynamic
-- ----------------------------
INSERT INTO `dynamic` VALUES ('1', '天气真好', '1001');
INSERT INTO `dynamic` VALUES ('2', '坤坤去哪了', '1002');
INSERT INTO `dynamic` VALUES ('3', '来打篮球吗', '1003');
INSERT INTO `dynamic` VALUES ('4', '吃饭了', '1003');

-- ----------------------------
-- Table structure for dy_info
-- ----------------------------
DROP TABLE IF EXISTS `dy_info`;
//用户动态和话题关系表
re_id 记录id
topic_id 话题id
dynamic_id 动态id
CREATE TABLE `dy_info` (
  `re_id` int(11) DEFAULT NULL,
  `topic_id` int(11) DEFAULT NULL,
  `dynamic_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of dy_info
-- ----------------------------
INSERT INTO `dy_info` VALUES ('1', '1', '1');
INSERT INTO `dy_info` VALUES ('2', '1', '2');
INSERT INTO `dy_info` VALUES ('3', '2', '3');
INSERT INTO `dy_info` VALUES ('4', '3', '4');

-- ----------------------------
-- Table structure for topic
-- ----------------------------
DROP TABLE IF EXISTS `topic`;
//话题表 用户发表动态会关联一个话题
topic_id 话题id
topic_con 话题内容
CREATE TABLE `topic` (
  `topic_id` int(11) DEFAULT NULL,
  `topic_con` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of topic
-- ----------------------------
INSERT INTO `topic` VALUES ('1', '唱歌');
INSERT INTO `topic` VALUES ('2', '跳舞');
INSERT INTO `topic` VALUES ('3', '电影');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
//用户表 user_id 用户id user_name 用户名
CREATE TABLE `user` (
  `user_id` int(11) DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1001', '蔡徐坤');
INSERT INTO `user` VALUES ('1002', '蔡徐坤他奶奶');
INSERT INTO `user` VALUES ('1003', '蔡徐坤他爷爷');
INSERT INTO `user` VALUES ('1004', '蔡徐坤他爸爸');

现在有个需求 我想按话题表分类 求每个话题的前三个动态的内容 和 统计每个话题动态的的数量 用一条语句这个该怎么写呢 想了一下午了 一开始想用分组 但是分组好像只能每个话题只有一条记录  公司遇到的这个需求 我把表简化了一下 求大佬指点

ammo

select t1.topic_con,group_concat(t1.dynamic_con),sum(cc) from
(
(select y.dynamic_con,count(1) cc,t.topic_con from dy_info d
left join topic t on d.topic_id = t.topic_id
left join dynamic y on d.dynamic_id = y.dynamic_id
left join user u on y.user_id = u.user_id
where t.topic_id = '1'
group by y.dynamic_con,t.topic_con
order by cc desc
limit 0,3)
union all
(select y.dynamic_con,count(1) cc,t.topic_con from dy_info d
left join topic t on d.topic_id = t.topic_id
left join dynamic y on d.dynamic_id = y.dynamic_id
left join user u on y.user_id = u.user_id
where t.topic_id = '2'
group by y.dynamic_con,t.topic_con
order by cc desc
limit 0,3)
union all
(select y.dynamic_con,count(1) cc,t.topic_con from dy_info d
left join topic t on d.topic_id = t.topic_id
left join dynamic y on d.dynamic_id = y.dynamic_id
left join user u on y.user_id = u.user_id
where t.topic_id = '3'
group by y.dynamic_con,t.topic_con
order by cc desc
limit 0,3)
) t1
group by t1.topic_con

l5666595

SELECT
        a.*,c.cc
FROM
        dy_info a
        LEFT JOIN ( SELECT topic_id, count( 1 ) ccFROM dy_info GROUP BY topic_id ) c ON a.topic_id = c.topic_id
WHERE
        ( SELECT COUNT( 1 ) FROM dy_info b WHERE a.topic_id = b.topic_id AND a.dynamic_id > b.dynamic_id ) < 3
ORDER BY
        a.topic_id;   
分组合计放最后一列。。不知道符不符合你要求了。。