测试数据
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`object` varchar(255) DEFAULT NULL,
`number` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '张三', '语文', '80');
INSERT INTO `user` VALUES ('2', '李四', '数学', '65');
INSERT INTO `user` VALUES ('3', '王五', '英语', '70');
INSERT INTO `user` VALUES ('4', '张三', '数学', '39');
INSERT INTO `user` VALUES ('5', '李四', '语文', '12');
INSERT INTO `user` VALUES ('6', '王五', '语文', '13');
INSERT INTO `user` VALUES ('7', '张三', '英语', '39');
INSERT INTO `user` VALUES ('8', '李四', '英语', '89');
INSERT INTO `user` VALUES ('9', '王五', '数学', '11');
1 查询每一位同学的总分。
按名字分组。对每一组使用sum函数求和。
select name,sum(number) AS total from user group by name
2 求每一门的最高成绩。
参考网上一部分的,自己总结了一部分。
-- 好理解的。分组,使用max求出每一组中的最高
select object,max(number) from user GROUP BY object
Select * from user where id in(select id from user GROUP BY object ORDER BY number)
-- 不知是对好是错
select name,object,number from user GROUP BY object ORDER BY number
-- 个人版,此种写法过程类似于数据的匹配的过程,将相同的科目合
-- 在一起后进行排序,去最高的一行记录
Select object,number from user u where id=(
select id from user where u.object=object limit 1 ORDER BY number desc)
-- 按照分组找出分数最高的行,逻辑位置为number<u.number,该出与上类似
Select * from user u where exists(
select 1 from user where object=u.object and number<u.number)
GROUP BY object
先排序后,再分组,这样MySQL就会取的是每一组的第一条记录。
SELECT concurrent,create_time FROM (
SELECT max(con.concurrent),DATE_FORMAT(con.create_time,'%Y-%m-% d')
FROM tb_cloud_ai_concurrent
WHERE 1=1
AND type=#{type}
AND create_time BETWEEN #{beginDate} AND #{endDate}
AND ip=#{ip}
ORDER BY concurrent DESC
) AS con
GROUP BY
DATE_FORMAT(con.create_time,'%Y-%m-% d')
ORDER BY con.create_time ASC
参考:https://blog.51cto.com/huanghualiang/1252630
未完待续。。。