-
- Ganjr 2022-07-02
问题：如何查询出四人组中打怪最多的日期？
- 0赞 · 0采集
-
- Ganjr 2022-07-02
使用 JOIN 优化聚合子查询
引入一张新表，按日期记录四人组中每个人打怪的数量
- 0赞 · 0采集
-
- 斜杠男人 2021-02-21
使用join优化聚合子查询
1）、having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录
-
截图0赞 · 0采集
-
- 斜杠男人 2021-02-21
聚合子查询
-
截图0赞 · 0采集
-
- weixin_慕数据7341184 2020-11-10
查询打怪最多的日期
select a.user_name,b.timestr,b.kills from user1 a join user_kills b ON a.id=b.user_id where b.kills=(select MAX(c.kills) from user_kills c where c.user_id=b.user_id);
优化后:
select a.user_name,b.timestr,b.kills from user1 a join user_kills b ON a.id=b.user_id
join user_kills b ON c.user_id=b.user_id
group by a.user_name,b.timestr,b.kills having b.kills=max(c.kills);
-
截图0赞 · 0采集
-
- weixin_慕数据7341184 2020-11-10
join 聚合查询
插入新表
-
截图0赞 · 0采集
-
- 慕粉1100306574 2020-09-28
- having
-
截图0赞 · 0采集
-
- CharlesXJZ 2020-04-23
避免子查询：
-
截图0赞 · 0采集
-
- CharlesXJZ 2020-04-23
使用JOIN聚合子查询：
-
截图0赞 · 0采集
-
- 慕数据5164829 2020-03-28
用join 优化聚合子查询
mysql> select a.username,b.kills from tbuser1 a
-> join tbuser2 b on a.id=b.username
-> join tbuser2 c on c.username=b.username
-> group by a.username,b.kills
-> having b.kills=max(c.kills);
用join优化连表更新;
update tbuser2 a join tbuser1 b on a.username=b.username set a.username=b.id;
- 0赞 · 0采集
-
- 慕粉1202776 2019-08-16
使用join语句优化子查询
-
截图0赞 · 0采集
-
- SHAWN_LAU 2019-06-25
如何查询出打怪四人组中打怪最多的日期？
使用子查询语句：
select a.user_name,b.timestr,b.kills from user1 as a left join user_kills as b on a.id = b.user_id where b.kills = (select max(c.kills) from user_kills as c where b.user_id = c.user_id);
在子查询中添加 b.user_id = c.user_id 目的在于 在 “悟空”“八戒”“沙僧”中分组取出最大值，若是直接删除的话，只能在所有数据中取出最大值。
2. 优化使用join 语句
select a.user_name , b.timestr , b.kills from user1 a join user_kills b on a.id = b.user_id join user_kills c on c.user_id = b.user_id group by a.user_name , b.timestr , b.kills having b.kills = MAX(c.kills)
?
- 0赞 · 0采集
-
- 我私发我 2019-06-15
join 优化聚合子查询
-
截图0赞 · 0采集
-
- yemomo 2019-04-04
该课程可以学习2-7和2-8来了解join的应用
- 0赞 · 0采集
-
- Being_yourself 2019-03-02
含义：找出c列的最大值，对应到b列的日期。
-
截图0赞 · 0采集
-
- 慕设计6334224 2018-10-17
1、使用子查询
select a.user_name,b.timestr,b.kills
from user1 a join user_kills b
on a.id=b.user_id
where b.kills =(select max(c.kills) from user_kills c where c.user_id = b.user_id)
2、使用join避免子查询
select a.user_name,b.timestr,b.kills
from user1 a
join user_kills b on a.id=b.user_id
join user_kills c on c.user_id = b.user_id
group by a.user_name,b.timestr,b,kills
having b.kills=max(c.kills)
- 0赞 · 0采集
-
- helloap 2018-09-08
优化聚合子查询
-
截图0赞 · 0采集
-
- qq_柯九思_0 2018-08-12
select a.user_name,b.timerstr,.b.kills from user user1 a Join user_kills b on a.id=b.userid where
b.kills（select Max(c.kills) from user_kills c where c.user_id=b.user_id）
- 0赞 · 0采集
-
- 陌丶沫笑 2018-08-03
where 后边进行聚合查询
- 0赞 · 0采集
-
- qq_星_20 2018-07-29
join优化聚合子查询
- 0赞 · 0采集
-
- 我是猫_夏 2018-07-20
聚合子查询
-
截图0赞 · 0采集
-
- BirdOfV 2018-05-26
@MySQL---JOIN优化聚合子查询
1.一般写法，未能解决SELECT g.user_name,MAX(k.kills),k.time FROM `group` AS g JOIN kills AS k ON g.id=k.user_id GROUP BY g.user_name ORDER BY g.user_name;
2.使用JOIN优化聚合子查询
SELECT a.user_name,b.timestr,b.kills FROM user1 a JOIN user_kills b ON a.id = b.user_id JOIN user_kills c ON c.user_id = b.user_id GROUP BY a.user_name,b.timestr,b.kills HAVING b.kills = MAX(c.kills);
-
截图0赞 · 0采集
-
- 扫一扫 2018-04-15
使用join和having避免重复查询
-
截图0赞 · 0采集
-
- EnchantF 2018-04-08
-
截图0赞 · 0采集
-
- qq_LostInTheEch_0 2018-03-24
SELECT g.user_name,MAX(k.kills),k.time FROM `group` AS g JOIN kills AS k ON g.id=k.user_id GROUP BY g.user_name ORDER BY g.user_name;
SELECT g.user_name,k.kills,k.time FROM `group` AS g JOIN kills AS k ON g.id=k.user_id JOIN kills AS k1 ON g.id=k1.user_id GROUP BY g.user_name,k.kills HAVING k.kills=MAX(k1.kills) ORDER BY k.kills;
-
截图0赞 · 0采集
-
- 华振宇 2018-03-14
- 如何查询出四人组中打怪最多的日期？ 其实这个问题是可以用分组来解决的。 语句如下： select a.user_name,b.timestr,max(b.kills) from user1 a inner join user_kills b on a.id = b.user_id GROUP BY a.user_name;
-
截图0赞 · 0采集
-
- 鼓手 2017-12-21
- Create Table CREATE TABLE `user_kills` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `user_id` smallint(5) unsigned DEFAULT NULL, `timestr` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `kills` smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 INSERT INTO user_kills (user_id, timestr, kills) VALUES ('2','2013-01-10 00:00:00', '10'); INSERT INTO user_kills (user_id, timestr, kills) VALUES ('2','2013-02-01 00:00:00', '2'); INSERT INTO user_kills (user_id, timestr, kills) VALUES ('2','2013-02-05 00:00:00', '12'); INSERT INTO user_kills (user_id, timestr, kills) VALUES ('4','2013-01-10 00:00:00', '3'); INSERT INTO user_kills (user_id, timestr, kills) VALUES ('2','2013-02-11 00:00:00', '5'); INSERT INTO user_kills (user_id, timestr, kills) VALUES ('2','2013-02-06 00:00:00', '1'); INSERT INTO user_kills (user_id, timestr, kills) VALUES ('3','2013-01-11 00:00:00', '20');
- 0赞 · 0采集
-
- DomainRocker 2017-11-11
- join优化聚合子查询： -- 使用join优化聚合子查询 -- 如何查询tangtang表中打怪最多的日期 -- select a.user_name ,b.timestr,b.kills from tangtang a join killscount b on a.id = b.user_id -- where b.kills=(select max(c.kills) from killscount c where c.user_id = b.user_id) -- select * from killscount b join tangtang a on b.user_id = a.id order by kills DESC -- select a.user_name,b.timestr,max(b.kills) from tangtang a join killscount b on a.id = b.user_id group by a.user_name,b.timestr; -- 优化后语句 避免子查询 -- select a.user_name,b.timestr,b.kills from tangtang a -- join killscount b on a.id = b.user_id -- join killscount c on c.user_id = b.user_id -- GROUP BY a.user_name,b.timestr,b.kills -- HAVING b.kills = max(c.kills)
- 0赞 · 0采集
-
- 慕UI2044250 2017-10-17
- group by 条件1，条件2（分组）
- 0赞 · 0采集
-
- qq_霸气_亚强_03756285 2017-10-14
- 使用joint group by having 优化聚合函数查询
-
截图0赞 · 0采集
