SQL刷题记录

最新推荐文章于 2023-06-28 08:38:42 发布

lc11535

最新推荐文章于 2023-06-28 08:38:42 发布

阅读量619

点赞数

分类专栏： # MySQL INTERVIEW

本文链接：https://blog.csdn.net/lc11535/article/details/115576341

版权

这篇博客详细记录了在NowCoder上的SQL刷题经历，重点讲解了MySQL中的字符串处理、OVER函数、CASE关键字的用法，以及在不同场景下IN和EXISTS的比较。内容涵盖字符串计数、窗口函数的应用、分组排名和条件判断，并提供了多个示例和代码演示。

摘要由CSDN通过智能技术生成

nowcoder SQL刷题记录：

https://www.nowcoder.com/ta/sql

复习题目：12, 18, 21, 44, 57, 88， 60， 61， 91， 88

60虽然做出来了，但是对over中的partition by 和order by 还是不够理解，可以尝试不用window function可以做出来吗

61虽然做出来了，但是学习子查询欠缺

91虽然做出来了，但是with语法并不会

88有大佬使用了一个NB的解法，这个题和76题并不同，76题是单个条目取中位数的数据，但是88是很多条目累计的和，如果使用76的方法肯定是行不通的

这个题要么取巧，要么还是好好复习

如果over中使用了order by 则之后的clause不用再使用order by语句，order by在window中使用会导致所有的行都有效

【Mysql】查找字符串'10,A,B' 中逗号','出现的次数cnt。

题目描述：查找字符串'10,A,B' 中逗号','出现的次数cnt。。
知识点总结：

统计字符串长度：
char_length('string')/char_length(column_name)
1、返回值为字符串string或者对应字段长度，长度的单位为字符，一个多字节字符（例如，汉字）算作一个单字符；
2、不管汉字还是数字或者是字母都算是一个字符；
3、任何编码下，多字节字符都算是一个字符；
参考资料来源：https://blog.csdn.net/iris_xuting/article/details/53763894

length('string')/length(column_name)
1、utf8字符集编码下,一个汉字是算三个字符,一个数字或字母算一个字符。
2、其他编码下,一个汉字算两个字符, 一个数字或字母算一个字符。

字符串替换：REPLACE(s,s1,s2)，将字符串 s2 替代字符串 s 中的字符串 s1
MySQL常用函数：https://www.runoob.com/mysql/mysql-functions.html

代码：

1	`select char_length("10,A,B")-char_length(replace("10,A,B",",",""))`

select s.emp_no, a.max-min(s.salary) growth from (select emp_no, salary max from salaries where to_date='9999-01-01') a 
LEFT JOIN salaries s on a.emp_no=s.emp_no GROUP BY s.emp_no ORDER BY growth;

select s.emp_no, a.max-min(s.salary) growth from (select emp_no, salary max from salaries where to_date='9999-01-01') a LEFT JOIN salaries s on a.emp_no=s.emp_no GROUP BY s.emp_no ORDER BY growth
> 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'nowcoder.salaries.salary' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

对于下面的SQL，并不是不在group by 后边的字段出现在select中就会出现错误，但是必须是来自同一张表才可以

select c.name, count(f.film_id) from film f join film_category fc on f.film_id=fc.film_id

join category c on c.category_id=fc.category_id

join (

select fc.category_id, count(fc.film_id) cnt from film f join film_category fc on f.film_id=fc.film_id

group by fc.category_id

) r on r.category_id= fc.category_id

where f.description like "%robot%" and r.cnt >= 5 group by c.category_id;

非group by 的列查询出来的结果是不确定的，不是想要的那行数据

group by 之后的hvaing也只能出现group by后的字段吗？> 1054 - Unknown column 'client_id' in 'IN/ALL/ANY subquery'

select job, count(id) cnt from grade

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'nowcoder.grade.job'; this is incompatible with sql_mode=only_full_group_by

MySQL over函数的用法

over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。
例如：employees表中，有两个部门的记录：department_id ＝10和20
select department_id，rank（） over（partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名，在部门20中进行薪水排名。如果是partition by org_id，则是在整个公司内进行排名。

以下是个人见解：

sql中的over函数和row_numbert()函数配合使用，可生成行号。可对某一列的值进行排序，对于相同值的数据行进行分组排序。

执行语句：select row_number() over(order by AID DESC) as rowid,* from bb

CASE关键字有两种使用方法

分别是‘简单case函数’和‘case搜索函数’

简单case函数

CASE sex

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE '其他' END

case搜索函数

CASE

WHEN sex='1' THEN '男'

WHEN sex='2' THEN '女'

ELSE '其他' END

分析说明

1. 简单case函数是case搜索函数的真子集
  - 简单case函数的使用方法与一些高级语言（如：java）中的switch语句相似:CASE给定匹配字段，WHEN给出具体的字段值，如果匹配到后返回THEN值。
  - 简单case函数其实就是case搜索函数的‘=’逻辑的实现。case搜索函数可以实现简单case函数的所有功能，而简单case函数却不可实现case搜索函数的‘=’逻辑以外的功能。
2. case函数匹配原则
  - case函数与switch的不同在于case仅返回第一个匹配到的结果，而switch则会在没有中断的情况下继续后面的判断，将会执行所有匹配的结果。
3. case搜索函数比简单case函数更加灵活
  - case搜索函数与简单case函数相比的灵活之处在于可以在WHEN中书写判断式。

1、等值转换

SELECT user_name,( CASE WHEN sex = 0 THEN '男人' WHEN sex = 1 THEN '女人' ELSE '中性人' END ) AS 性别 FROM imooc_goddess;

SELECT user_name,( CASE sex WHEN 0 THEN '男人' WHEN 1 THEN '女人' ELSE null END ) AS 性别 FROM imooc_goddess;

2、范围转换

select user_name, (case when age BETWEEN 0 and 18 then '未成年' when age BETWEEN 18 and 30 then '成年' when age BETWEEN 30 and 50 then '中年人' else '老年人' end) as 年龄段
FROM imooc_goddess;

select user_name,(case when age>0 and age<=18 then '未成年' when age >18 and age<=30 then '成年' when age >30 and age<=50 then '中年人' else '老年人' end) as 年龄段 FROM imooc_goddess;

3、行转列

select name , (case when sub = 'english' then score else 0 end ) 'english' ,
(case when sub = 'maths' then score else 0 end) 'maths' ,
(case when sub = 'chinese' then score else 0 end) 'chinese'
FROM stu;

4、聚合函数使用case when

select name , sum((case when sub = 'english' then score else 0 end )) 'english' ,
sum((case when sub = 'maths' then score else 0 end)) 'maths' ,
sum((case when sub = 'chinese' then score else 0 end)) 'chinese'
FROM stu GROUP BY name ;

1.IF

表达式：IF( expr1 , expr2 , expr3 )

expr1条件，条件为true，则值是expr2 ，false，值就是expr3

2. IFNULL

表达式：IFNULL( expr1 , expr2)

在 expr1 的值不为 NULL的情况下都返回 expr1，否则返回 expr2

MySQL为查询结果添加序号

时间:2018-12-19

本文章向大家介绍MySQL为查询结果添加序号，主要包括MySQL为查询结果添加序号使用实例、应用技巧、基本知识点总结和需要注意事项，具有一定的参考价值，需要的朋友可以参考一下。

查询结果
现在的要求是：根据salary的值排名，并添加序号。
有三种添加序号的方式：

顺序排名
顺序排名，有相同的salary采取并列，保持排名不变
顺序排名，采取并列，且排名不变的salary仍然占用一个位置

一、不考虑并列( “:=”表示赋值)

select
s.emp_no,s.salary,
 @n:=@n+1 rank
from salaries s, (select @n:= 0) d
where s.to_date='9999-01-01' order by s.salary desc

查询结果
在这里插入图片描述
二、采取并列（需要添加一个字段来保存上一次的salary值进行比较，如果相同，排名不变；如果不同，排名+1）

select
s.emp_no,s.salary,
case
when @t=s.salary then @n
when @t:=s.salary then @n:=@n+1
end rank
from salaries s, (select @n:= 0,@t:= -1) d
where s.to_date='9999-01-01' order by s.salary desc

还有一种简便写法

SELECT emp_no,salary,
@rank := @rank + (@pre <> (@pre := salary)) Rank
FROM salaries, (SELECT @rank := 0, @pre := -1) INIT
WHERE to_date = '9999-01-01
order by salary

mysql分组排序加序号（不用存储过程，就简简单单sql语句哦）

一建表

CREATE TABLE `my_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_code` varchar(255) DEFAULT NULL,
  `code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

二模拟数据

INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('01', '001');
INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('01', '002');
INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('02', '001');
INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('01', '003');
INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('02', '002');
INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('03', '001');
INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('04', '001');

查询结果如下：

三不分组加序号

select  (@i := @i + 1) rownum,my_test.*  from    my_test , (SELECT  @i := 0) AS a  group  by   parent_code ,code ,id  order  by   parent_code

结果如下：

解释一下这个地方用了@i变量刚开始的让 @i=0 然后每查询一条让 @i+=1

四分组排序加序号了

刚开始的没思路，就度娘了，有用存储过程创建临时表插入临时表实现的，还有用存储过程游标实现，对于好久没动sql，而且之前也没写过mysql 查询的淫来说好复杂，

好囧，赶脚要再我女神面前丢人了,but 多谢上天眷顾，查看我女神聊天记录的时候，灵感来了，为什么不继续发掘下变量的作用呢。

于是再定义一个变量@pre_parent_code:='' 再存上一个 parent_code ，只要 pre_parent_code不等于当前的parent_code 让 @i:=0 else @i+=1 就ok了

select 
                        -- rownum  判断  @pre_parent_code是否和当前的parent_code一样 ，true:让 @i+=1 false:重置@i
                    
                        (@i := case  when  @pre_parent_code=parent_code then @i + 1 else 1 end )  rownum,