-
内连接（INNER） 外连接（FULL OUTER） 左连接（LEFT OUTER） 右连接（RIGHT OUTER）查看全部
-
SQL分类查看全部
-
DML查看全部
-
-- 使用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)查看全部
-
-- 交叉连接 又称为笛卡尔连接 如果A,B两个集合，交叉连接就记为A乘以B，也叫叉乘。 -- tangtang表4条记录，sunsun表5条记录，4乘以5，共20条记录 -- select a.user_name,a.over ,b.user_name,b.over from tangtang a CROSS join sunsun b -- 如何更新使用过滤条件中包括自身的表 -- update tangtang set over = '齐天大圣' where tangtang.user_name in (select b.user_name from tangtang a inner join sunsun b on a.user_name = b.user_name) -- mysql不支持上边的语句查询 ，sqlserver完全支持。 下边，是功能一样，并且支持mysql使用的语句。 -- update tangtang a join -- (select b.user_name from tangtang a inner join sunsun b on a.user_name = b.user_name) b on a.user_name = b.user_name set a.over = '齐天大圣' -- 使用join优化子查询 -- 优化前 select a.user_name,a.over,(select over from sunsun b where a.user_name = b.user_name) as over2 from tangtang a; -- 优化后select a.user_name,a.over,b.over from tangtang a left join sunsun b on a.user_name = b.user_name查看全部
-
-- 内连接 -- select * from tangtang a inner join sunsun b where a.id=b.id -- 左连接1 -- select a.user_name,a.over,b.over from tangtang a -- left JOIN sunsun b on a.user_name = b.user_name -- 左连接2 查询只存在a表中的数据 -- select a.user_name,a.over,b.over from tangtang a -- left JOIN sunsun b on a.user_name = b.user_name -- where b.user_name is not null -- 右连接1 -- select b.user_name,b.over,a.over from tangtang a -- right JOIN sunsun b on a.user_name = b.user_name -- 右连接2 查询只存在b表中的数据 -- select b.user_name,b.over,a.over from tangtang a -- right JOIN sunsun b on a.user_name = b.user_name -- where a.user_name is not null -- full join mysql不支持full join链接查询，用union all 代替 full jion 查询 -- select a.user_name,a.over,b.over from tangtang a -- left JOIN sunsun b on a.user_name = b.user_name -- union all -- select b.user_name,b.over,a.over from tangtang a -- right JOIN sunsun b on a.user_name = b.user_name查看全部
-
-- 注意自己是否需要建库 -- create database if not exists mysqldv1; -- 新建 user1 表 create table if not exists user1 ( id smallint unsigned primary key auto_increment, user_name varchar(40), over varchar(40) ); -- 新建 user2 表 create table if not exists user2 ( id smallint unsigned primary key auto_increment, user_name varchar(40), over varchar(40) ); -- 新建 user_kills 表 create table if not exists user_kills ( id smallint unsigned primary key auto_increment, user_id smallint unsigned, timestr timestamp default CURRENT_TIMESTAMP, kills smallint unsigned );查看全部
-
select d.user_name,c.timestr,kills FROM (select user_id,timrstr,kills,(SELECT COUNTA(*) FROM user_kils b WHERE b.user_id=a.user_id AND a.kills<=b.kills) AS cnt FROM user_kills a GROUP BY user_id,timestr,kills) c JOIN user1 dON c.user_id=d.id WHERE cnt <= 2查看全部
-
更新的表不能出现在from中，需要使用join查看全部
-
开发过程中最经常使用到的语句查看全部
-
解决方案2：实现分组选择查看全部
-
解决方案1：使用WITH 表名 AS ()，不过MySQL中不支持ROW_NUMBER() OVER(PARTITION BY 字段名 )查看全部
-
出现的问题：查看全部
-
场景：查看全部
-
使用JOIN来优化聚合子查询：查看全部
举报
0/150
提交
取消