qq_43314839的博客

https://blog.youkuaiyun.com/tayngh/article/details/99684035在这个博客的基础上补充一下。这是用on的输出结果：这是用where的输出结果：结合上面链接的博客的文字内容理解，就明朗了。

题1https://www.nowcoder.com/practice/a5475ed3b5ab4de58e2ea426b4b2db76?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0select exam_id, substring_index(tag, ',', 1) as tag, substring_index(substring_index(tag, ',', 2),

题1https://www.nowcoder.com/practice/fbe36305c6dd4954a05cc2f2f12e4f4a?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0select uid, nick_name, register_timefrom user_infoorder by register_time asclimit 3select uid, nick_name, r

题1https://www.nowcoder.com/practice/2ed07ff8f67a474d90523b88402e401b?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0select uid, nick_name, achievement FROM user_info left join exam_record using(uid) lef

文章目录题目1题2题目1https://www.nowcoder.com/practice/69fc2b1df4144c0991e4b8280d8aba27?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0先以子表写法：select exam_id, sum(incomplete) as incomplete_cnt, round(sum(incomplete) / count

题1https://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0SELECT uid, exam_id, ROUND(avg(if(score_cnt>1,(score-min_score)/(max_score-min_score)*100,score)),0) avg_

文章目录题1题2题3题4题5题1https://www.nowcoder.com/practice/255aa1863fe14aa88694c09ebbc1dbca?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0SELECT tag, uid, rankingFROM ( SELECT tag, uid, ROW_NUMBER()

题1https://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0select uid, exam_cnt, if(question_cnt is null, 0, question_cnt)from (select uid,

题1https://www.nowcoder.com/practice/203d0aed8928429a8978185d9a03babc?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0解1(select exam_id as tid, count(distinct uid) as uv, count(exam_id) as pvFROM exam_recordgro

文章目录题1题2法1法2：两次left join表连接：题3法1法2：使用in运算符分步查询题1https://www.nowcoder.com/practice/b1d13efcfa0c4ecea517afbdb9090845?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0select tag, count(tag) as tag_cntfrom exam_record

知识coalescehttps://blog.youkuaiyun.com/yilulvxing/article/details/86595725COALESCE是一个函数，coalesce (expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。SQL实例select coalesce(success_cnt, 1) from tableA当success_cnt 为null值的时候，将返回1，否则将返回succes

文章目录知识题目1常规解法1：解法2：解法3：解法4：写法５：知识聚合函数：max、min、count、avg、sum分组函数：group by聚合函数会自动忽略值为null的行聚合函数只能直接加到SELECT, HAVING, GROUP BY 后面题目1https://www.nowcoder.com/practice/a690f76a718242fd80757115d305be45?tpId=240&tqId=2180959&ru=/ta/sql-advanced&a

文章目录表的创建、修改与删除表的创建直接创建表从另一张表复制其表结构来创建表从另一张表的查询结果来创建表表的修改表的删除表的索引的创建、删除索引的创建索引的删除表的创建、修改与删除表的创建直接创建表CREATE TABLE[IF NOT EXISTS] tb_name -- 不存在才创建，存在正常返回。(column_name1 data_type1 -- 列名和类型必选 [ PRIMARY KEY -- 可选的约束，主键 | FOREIGN KEY -- 外键，引用其他表的键值

这里写自定义目录标题插入记录题目1题目2题目3更新记录题目1删除记录插入记录插入记录的方式汇总：普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, …)普通插入（限定字段）：INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …)多条一次性插入：INSERT INTO table_name (column1, column2, …) VALUES (v

窗口函数详细语法：https://zhuanlan.zhihu.com/p/92654574题目：https://www.nowcoder.com/practice/90778f5ab7d64d35a40dc1095ff79065?tpId=199&tags=&title=&difficulty=0&judgeStatus=0&rp=0法1：使用窗口函数row_number：select device_id, university, gpafrom

文章目录字符串截取之substring_index、substring(substr)题目１：题目２：题目3：字符串截取之substring_index、substring(substr)转载于：https://www.cnblogs.com/mqxs/p/7380933.html题目１：https://www.nowcoder.com/practice/f04189f92f8d4f6fa0f383d413af7cb8?tpId=199&tags=&title=&diffi

文章目录题目题解：https://www.nowcoder.com/practice/30f9f470390a4a8a8dd3b8e1f8c7a9fa解法1：利用CASE函数select case when age<25 or isnull(age) then "25岁以下" # 注意这里写age is not null,是错的 when age>=25 then '25岁及以上' end as age_cut, count(*) as

多表查询 - 多表连接（原文：https://www.nowcoder.com/practice/55f3d94c3f4d47b69833b335867c06c1）若一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询包括内连接、外连接和交叉连接等。连接查询中用于连接两个表的条件称为连接条件或连接谓词。一般格式为：内连接内连接语法如下：SELECT …FROM 表名[INNER] JOIN 被连接表ON 连接条件例1：查询每个学生及其班级的详细信息。SELECT * FR

对一个表查询所有列，使用select * 会降低运行速度且占用更多内存。建议用列名查询。查询结果去重（2种）：1. 使用DISTINCT：SELECT DISTINCT university from user_profile;2. 使用group by：SELECT university from user_profile GROUP BY university;使用group by比使用distinct的运行速度快一些，但是占用的内存大了点。查询结果限制返回行数（4种）：selec.