1. length 和char_length() 的区别？
# mysql 中 length() 函数: 一个汉字等于两个或者三个字符
# length函数用来计算普通字符的长度，他会把一个中文字符的长度按照设置的对应的字符集计算为2或3。
# 1、utf8字符集编码下，一个汉字是算三个字符，一个数字或字母算一个字符
# 2、其他编码下，一个汉字算两个字符，一个数字或字母算一个字符
select uid , nick_name , length(nick_name) ,char_length(nick_name) from user_info ;
# char_length()
# char_length函数可以计算unicode字符，包括中文等字符集的长度
# 1. 长度的单位为字符，一个多字节字符（例如：汉字）算作一个单字符
# 2、不管汉字还是数字或者是字母都算是一个字符；
# 3、任何编码下，多字节字符都算是一个字符；
# demo:
# +----+-----------------+-----------------+----------------------+
# |uid |nick_name |length(nick_name)|char_length(nick_name)|
# +----+-----------------+-----------------+----------------------+
# |1001|牛客1 |7 |3 |
# |1002|牛客2号 |10 |4 |
# |1003|牛客3号♂ |13 |5 |
# |1004|牛客4号 |10 |4 |
# |1005|牛客5678901234号 |19 |13 |
# |1006|牛客67890123456789号|23 |17 |
# +----+-----------------+-----------------+----------------------+1. 如何判断某个字段是正数，零还是 负数？ 用sign （）
sign( )函数：判断数值的正负性，如果数值是正数，返回值是1，如果该数值是负数，返回值是-1，如果该数值是 0，返回值也是0。
格式：
select sign(数值) from 表名1. 字符串获取 靠前，最后的几个字符
# RIGHT(s,n) 返回字符串 s 的后 n 个字符
# LEFT(s,n) 返回字符串 s 的前 n 个字符
# MID(s,n,len) 从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)
# LCASE(s) 将字符串 s 的所有字母变成小写字母
#
select right('abcde',2) ;
# de
select left ('abcde',2) ;
# abselect id ,name from girl where id in (1,2,3,4) ;
# 判断一个字段中 是否 含有字符串helloworld
# 注：MySQL中的模糊查询 like 和oracle中的instr()函数有同样的查询效果； 如下所示：
#
# select * from tableName a where name like '%helloworld%';
#
# select * from tableName a where instr(name,'helloworld')>0; --这两条语句的效果是一样的
# +--+----------------+
# |id|name |
# +--+----------------+
# |1 |慕容吹雪-慕容博-慕容吹雪 |
# |2 |慕容吹雪-慕容吹雪-慕容吹雪2 |
# |3 |慕容吹雪3-慕容吹雪-慕容吹雪3|
# |4 |慕容吹雪4 |
# +--+----------------+
#
# 截取 第一个 instr(str,sonStr) 获取 sonStr 在 str 中出现的下标，下标是从 1开始的
select id ,name ,substr(name,1,instr(name,'-')-1) from girl where id in (1,2,3,4) ;
# +--+----------------+--------------------------------+
# |id|name |substr(name,1,instr(name,'-')-1)|
# +--+----------------+--------------------------------+
# |1 |慕容吹雪-慕容博-慕容吹雪 |慕容吹雪 |
# |2 |慕容吹雪-慕容吹雪-慕容吹雪2 |慕容吹雪 |
# |3 |慕容吹雪3-慕容吹雪-慕容吹雪3|慕容吹雪3 |
# |4 |慕容吹雪4 | |
# +--+----------------+--------------------------------+
# left left(name,1,instr(name,'-')-1)
select id ,name ,left(name,instr(name,'-')-1) from girl where id in (1,2,3,4) ;
# +--+----------------+----------------------------+
# |id|name |left(name,instr(name,'-')-1)|
# +--+----------------+----------------------------+
# |1 |慕容吹雪-慕容博-慕容吹雪 |慕容吹雪 |
# |2 |慕容吹雪-慕容吹雪-慕容吹雪2 |慕容吹雪 |
# |3 |慕容吹雪3-慕容吹雪-慕容吹雪3|慕容吹雪3 |
# |4 |慕容吹雪4 | |
# +--+----------------+----------------------------+
# 元数据
# +--+----------------+
# |id|name |
# +--+----------------+
# |1 |慕容吹雪-慕容博-慕容吹雪 |
# |2 |慕容吹雪-慕容吹雪-慕容吹雪2 |
# |3 |慕容吹雪3-慕容吹雪-慕容吹雪3|
# |4 |慕容吹雪4 |
# +--+----------------+
# 截取 最后 一个
# (right ：下表是从右边开始的，从 1开始 ，找到第一个 ‘-’ 的下标，)
select id ,name , instr(name,'-'),right(name,instr(name,'-')-1) from girl where id in (1,2,3,4) ;
# +--+----------------+---------------+-----------------------------+
# |id|name |instr(name,'-')|right(name,instr(name,'-')-1)|
# +--+----------------+---------------+-----------------------------+
# |1 |慕容吹雪-慕容博-慕容吹雪 |5 |慕容吹雪 |
# |2 |慕容吹雪-慕容吹雪-慕容吹雪2 |5 |容吹雪2 |
# |3 |慕容吹雪3-慕容吹雪-慕容吹雪3|6 |慕容吹雪3 |
# |4 |慕容吹雪4 |0 | |
# +--+----------------+---------------+-----------------------------+
1. 小数点位数的处理：format 函数（date_format 是处理日期的）
# format 函数保留小数点 位数 format(123456.789,2)
# FORMAT(100.3111,0);//取整
# FORMAT函数返回一个字符串值
select score from exam_record;
# +-----+
# |score|
# +-----+
# |80 |
# |81 |
# |84 |
# |89 |
# |NULL |
# |NULL |
# |87 |
# |90 |
# |NULL |
# |50 |
# +-----+
# 保留整数
select FORMAT(score,0) from exam_record;
# +---------------+
# |FORMAT(score,0)|
# +---------------+
# |80 |
# |81 |
# |84 |
# |89 |
# |NULL |
# |NULL |
# |87 |
# |90 |
# |NULL |
# |50 |
# +---------------+
# 保留小数点一位
select FORMAT(score,1) from exam_record;
# +---------------+
# |FORMAT(score,1)|
# +---------------+
# |80.0 |
# |81.0 |
# |84.0 |
# |89.0 |
# |NULL |
# |NULL |
# |87.0 |
# |90.0 |
# |NULL |
# |50.0 |
# +---------------+
# 保留小数点二位
select FORMAT(score,2) from exam_record;
# +---------------+
# |FORMAT(score,2)|
# +---------------+
# |80.00 |
# |81.00 |
# |84.00 |
# |89.00 |
# |NULL |
# |NULL |
# |87.00 |
# |90.00 |
# |NULL |
# |50.00 |
# +---------------+1. 获取当月的最后一天，获取当月有几天，获取某一天的月份有几天
# mysql中LAST_DAY()函数是取某个月最后一天的日期。
# 获取当月最后一天
select last_day(curdate()) ;
# +-------------------+
# |last_day(curdate())|
# +-------------------+
# |2022-04-30 |
# +-------------------+
# 获取当月有几天
select date_format(last_day(curdate()), '%d') ;
# +--------------------------------------+
# |date_format(last_day(curdate()), '%d')|
# +--------------------------------------+
# |30 |
# +--------------------------------------+
select date_format(last_day('2022-01-08') , '%d') ;
# +------------------------------------------+
# |date_format(last_day('2022-01-08') , '%d')|
# +------------------------------------------+
# |31 |
# +------------------------------------------+1. 一张表 两个字段前缀是否一样
# 判断 一张表 两个字段的前缀是否一样 substr
select * from girl ;
# +--+-----+------+
# |id|name |boy_id|
# +--+-----+------+
# |1 |慕容吹雪 |1 |
# |2 |慕容吹雪2|1 |
# |3 |慕容吹雪3|3 |
# |4 |慕容吹雪4|3 |
# |5 |慕容吹雪5|3 |
# |6 |慕容吹雪6|NULL |
# |7 |D |NULL |
# |88|C |8 |
# +--+-----+------+
select name from girl ;
# +-----+
# |name |
# +-----+
# |慕容吹雪 |
# |慕容吹雪2|
# |慕容吹雪3|
# |慕容吹雪4|
# |慕容吹雪5|
# |慕容吹雪6|
# |D |
# |C |
# +-----+
# substr(a,index,length) index 从1开始，可以负数，不可以0，length 要截取长度
select id , substr(name,1,4) from girl ;
# +--+----------------+
# |id|substr(name,1,4)|
# +--+----------------+
# |1 |慕容吹雪 |
# |2 |慕容吹雪 |
# |3 |慕容吹雪 |
# |4 |慕容吹雪 |
# |5 |慕容吹雪 |
# |6 |慕容吹雪 |
# |7 |D |
# |88|C |
# +--+----------------+
select id , substr(name,2,3) from girl ;
# +--+----------------+
# |id|substr(name,2,3)|
# +--+----------------+
# |1 |容吹雪 |
# |2 |容吹雪 |
# |3 |容吹雪 |
# |4 |容吹雪 |
# |5 |容吹雪 |
# |6 |容吹雪 |
# |7 | |
# |88| |
# +--+----------------+
select id , substr(name,2,4) from girl ;
# +--+----------------+
# |id|substr(name,2,4)|
# +--+----------------+
# |1 |容吹雪 |
# |2 |容吹雪2 |
# |3 |容吹雪3 |
# |4 |容吹雪4 |
# |5 |容吹雪5 |
# |6 |容吹雪6 |
# |7 | |
# |88| |
# +--+----------------+
# 进阶： id 前几位一样的数据
select id from girl where substr(id,1,1) = substr(boy_id,1,1) ;
# +--+
# |id|
# +--+
# |1 |
# |3 |
# |88|
# +--+left(str，length)
# left(str,length) 函数： 左边开始的长度为 length 的子字符串
select * from girl ;
# +--+----------------+------+-----+
# |id|name |boy_id|name0|
# +--+----------------+------+-----+
# |1 |慕容吹雪-慕容博-慕容吹雪 |1 |慕容吹雪 |
# |2 |慕容吹雪-慕容吹雪-慕容吹雪2 |1 |慕容吹雪2|
# |3 |慕容吹雪3-慕容吹雪-慕容吹雪3|3 |慕容吹雪3|
# |4 |慕容吹雪4 |3 |NULL |
# |5 |慕容吹雪5 |3 |NULL |
# |6 |慕容吹雪6 |NULL |NULL |
# |7 |D |NULL |NULL |
# |88|C |8 |NULL |
# |99|慕容吹雪 |1 |慕容吹雪 |
# +--+----------------+------+-----+
select left(name,4) from girl ;
# +------------+
# |left(name,4)|
# +------------+
# |慕容吹雪 |
# |慕容吹雪 |
# |慕容吹雪 |
# |慕容吹雪 |
# |慕容吹雪 |
# |慕容吹雪 |
# |D |
# |C |
# |慕容吹雪 |
# +------------+
1. 字符串截取除了 substr
还有substring_index（）：适用场景 有分隔符的场景下用
substring_index()函数用来截取字符串
substring_index(str,delim,count)
str:要处理的字符串
delim:分隔符
count:计数
如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容，
相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容。
drop table if exists user_submit;
CREATE TABLE `user_submit` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`profile` varchar(100) NOT NULL,
`blog_url` varchar(100) NOT NULL
);
INSERT INTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');
INSERT INTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc');
INSERT INTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer');
INSERT INTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');
INSERT INTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');
select * from user_submit ;
# +--+---------+--------------------+---------------------+
# |id|device_id|profile |blog_url |
# +--+---------+--------------------+---------------------+
# |1 |2138 |180cm,75kg,27,male |http:/url/bisdgboy777|
# |1 |3214 |165cm,45kg,26,female|http:/url/dkittycc |
# |1 |6543 |178cm,65kg,25,male |http:/url/tigaer |
# |1 |4321 |171cm,55kg,23,female|http:/url/uhsksd |
# |1 |2131 |168cm,45kg,22,female|http:/url/sysdney |
# +--+---------+--------------------+---------------------+
# 运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果
select profile from user_submit ;
# +--------------------+
# |profile |
# +--------------------+
# |180cm,75kg,27,male |
# |165cm,45kg,26,female|
# |178cm,65kg,25,male |
# |171cm,55kg,23,female|
# |168cm,45kg,22,female|
# +--------------------+
# 法① ：
select substr(profile,12,2) age ,count(1) number from user_submit group by substr(profile,12,2);
# 法② ：
#
# substring_index()函数用来截取字符串
#
# substring_index(str,delim,count)
# str:要处理的字符串
# delim:分隔符
# count:计数
# 如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容，
#
# 相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容。
select substring_index(profile,',',3) age ,count(1) number from user_submit group by substring_index(profile,',',3);
# /+-------------+------+
# |age |number|
# +-------------+------+
# |180cm,75kg,27|1 |
# |165cm,45kg,26|1 |
# |178cm,65kg,25|1 |
# |171cm,55kg,23|1 |
# |168cm,45kg,22|1 |
# +-------------+------+
# 截取尾
select substring_index(substring_index(profile, ',', 3), ',', -1) age, count(1) number
from user_submit
group by substring_index(substring_index(profile, ',', 3), ',', -1);
# +---+------+
# |age|number|
# +---+------+
# |27 |1 |
# |26 |1 |
# |25 |1 |
# |23 |1 |
# |22 |1 |
# +---+------+
# 从一个字段中 比如 算法,medium,80 的字段 ，截取 出 算法,medium,80 作为三列的字段
# 技巧： substring_index(,,) ,算法 和 80 是首 和 尾 ，很容易截取， medium 在中间不好截取，技巧 先截取 算法,medium 作为 字符串 A ，然后 对 字符串 A截取尾
drop table if exists examination_info,exam_record;
CREATE TABLE examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, '算法', 'hard', 60, '2020-01-01 10:00:00'),
(9002, '算法', 'hard', 80, '2020-01-01 10:00:00'),
(9003, 'SQL', 'medium', 70, '2020-01-01 10:00:00'),
(9004, '算法,medium,80','', 0, '2020-01-01 10:00:00');
select * from examination_info;
# +--+-------+------------+----------+--------+-------------------+
# |id|exam_id|tag |difficulty|duration|release_time |
# +--+-------+------------+----------+--------+-------------------+
# |1 |9001 |算法 |hard |60 |2020-01-01 10:00:00|
# |2 |9002 |算法 |hard |80 |2020-01-01 10:00:00|
# |3 |9003 |SQL |medium |70 |2020-01-01 10:00:00|
# |4 |9004 |算法,medium,80| |0 |2020-01-01 10:00:00|
# +--+-------+------------+----------+--------+-------------------+
# 现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）
# 录题同学有一次手误将部分记录的试题类别tag、难度、时长同时录入到了tag字段，请帮忙找出这些录错了的记录，并拆分后按正确的列类型输出。
#
# 由示例数据结果输出如下：
# +-------+---+----------+--------+
# |exam_id|tag|difficulty|duration|
# +-------+---+----------+--------+
# |9004 |算法 |medium |80 |
# +-------+---+----------+--------+
select tag from examination_info where instr(tag,',') > 1 ;
# substring_index 也可以截取 算法,medium,80 到 medium：先截取 算法,medium，然后 算法,medium 从右面获取 medium
# instr or like 都可以
# 先获取 算法,medium
select substring_index(tag,',',2) from examination_info where instr(tag,',') > 1 ;
# +--------------------------+
# |substring_index(tag,',',2)|
# +--------------------------+
# |算法,medium |
# +--------------------------+
# 在 算法,medium 基础上获取 medium
select substring_index(substring_index(tag,',',2) ,',',-1) from examination_info where instr(tag,',') > 1 ;
# +---------------------------------------------------+
# |substring_index(substring_index(tag,',',2) ,',',-1)|
# +---------------------------------------------------+
# |medium |
# +---------------------------------------------------+
# 拼接结果
select exam_id, substring_index(tag,',',1) tag, substring_index(substring_index(tag, ',', 2), ',', -1) difficulty, substring_index(tag,',',-1) duration
from examination_info
where instr(tag, ',') > 1;
1.
MYSQL中coalesce函数处理 null 值
# MYSQL中coalesce函数的用法（类似于case when then 不过 是处理 null的）
# coalesce()：返回参数中的第一个非空表达式（从左向右依次类推）
# coalesce 英[ˌkəʊəˈles] 美[ˌkoʊəˈles]
# coalesce()：返回参数中的第一个非空表达式（从左向右依次类推）；
# 返回4
select coalesce(null,4,5);
# 返回3
select coalesce(null,null,3);
# 返回1
select coalesce(1,2,3);
# 如果传入的参数所有都是null，则返回null，比如
# 相当于case when then
SELECT COALESCE(NULL, NULL, NULL, NULL);
-- Return NULL
# 这个参数使用的场合为：假如某个字段默认是null，你想其返回的不是null，而是比如0或其他值，可以使用这个函数
select * from girl ;
# +--+----+------+
# |id|name|boy_id|
# +--+----+------+
# |1 |A |1 |
# |2 |B |1 |
# |3 |C |3 |
# |4 |C |3 |
# |5 |C |3 |
# |6 |D |NULL |
# |7 |D |NULL |
# |8 |C |2 |
# +--+----+------+
# 需求： 将 boy_id 是 null的 值 ，改成 -1 显示
SELECT COALESCE(boy_id,-1) as value from girl;
# +-----+
# |value|
# +-----+
# |1 |
# |1 |
# |3 |
# |3 |
# |3 |
# |-1 |
# |-1 |
# |2 |
# +-----+
# case when 实现
select case when boy_id is null then -1 else boy_id end boy_id
from girl;
# +------+
# |boy_id|
# +------+
# |1 |
# |1 |
# |3 |
# |3 |
# |3 |
# |-1 |
# |-1 |
# |2 |
# +------+1. 两个日期相差几天
# MySQL中的两个时间函数，用来做两个时间之间的对比(比较两个日期相差几天 DATEDIFF)
# TIMESTAMPDIFF，（如果当期时间和之前时间的分钟数相比较。大于1天，即等于1；小于1天，则等于0）
#
# DATEDIFF，（只按2016-11-16计算，不会加小时分钟数，按天计算）
# 2021-05-22 10:13:42
select DATEDIFF(NOW(),'2016-11-16 17:10:52');
# +-------------------------------------+
# |DATEDIFF(NOW(),'2016-11-16 17:10:52')|
# +-------------------------------------+
# |1648 |
# +-------------------------------------+
select DATEDIFF('2021-05-22 10:13:42','2016-11-16 17:10:52');
# +-----------------------------------------------------+
# |DATEDIFF('2021-05-22 10:13:42','2016-11-16 17:10:52')|
# +-----------------------------------------------------+
# |1648 |
# +-----------------------------------------------------+
select DATEDIFF('2021-05-22 10:13:42','2021-05-21 17:10:52');
# +-----------------------------------------------------+
# |DATEDIFF('2021-05-22 10:13:42','2021-05-21 17:10:52')|
# +-----------------------------------------------------+
# |1 |
# +-----------------------------------------------------+
select DATEDIFF('2021-05-22 10:13:42','2021-05-23 17:10:52');
# +-----------------------------------------------------+
# |DATEDIFF('2021-05-22 10:13:42','2021-05-23 17:10:52')|
# +-----------------------------------------------------+
# |-1 |
# +-----------------------------------------------------+
select DATEDIFF('2021-05-22 10:13:42','2021-05-31 17:10:52');
# +-----------------------------------------------------+
# |DATEDIFF('2021-05-22 10:13:42','2021-05-31 17:10:52')|
# +-----------------------------------------------------+
# |-9 |
# +-----------------------------------------------------+
# 当前时间 2021-05-22
select TIMESTAMPDIFF(DAY,'2021-11-16 10:13:42',NOW());
# +----------------------------------------------+
# |TIMESTAMPDIFF(DAY,'2021-11-16 10:13:42',NOW())|
# +----------------------------------------------+
# |-178 |
# +----------------------------------------------+
select TIMESTAMPDIFF(DAY,'2021-05-22 10:13:42',NOW());
# +----------------------------------------------+
# |TIMESTAMPDIFF(DAY,'2021-05-22 10:13:42',NOW())|
# +----------------------------------------------+
# |0 |
# +----------------------------------------------+
select TIMESTAMPDIFF(DAY,'2021-05-22 10:13:42','2021-05-22 10:13:46');
# +--------------------------------------------------------------+
# |TIMESTAMPDIFF(DAY,'2021-05-22 10:13:42','2021-05-22 10:13:46')|
# +--------------------------------------------------------------+
# |0 |
# +--------------------------------------------------------------+
# mysql 日期的比较(两个时间之间差了几年，几个季度，几个月，几个周，几个小时，几分钟等)
# datediff(date1,date2)：两个日期相减 date1 - date2，返回天数。
# TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)
# 函数共有三个参数，三个参数的取值分别是：
#
# interval：比较的类型，可取值FRAC_SECOND、SECOND、 MINUTE、 HOUR、 DAY、 WEEK、 MONTH、 QUARTER 或 YEAR
#
# datetime_expr1和datetime_expr2是待比较的两个时间，用后面的时间减去前面的时间
# demo
select datediff('2008-08-08', '2008-08-01');
# +------------------------------------+
# |datediff('2008-08-08', '2008-08-01')|
# +------------------------------------+
# |7 |
# +------------------------------------+
select TIMESTAMPDIFF(DAY,'2008-08-01','2008-08-08') ;
# +--------------------------------------------+
# |TIMESTAMPDIFF(DAY,'2008-08-01','2008-08-08')|
# +--------------------------------------------+
# |7 |
# +--------------------------------------------+
# 差几小时
select TIMESTAMPDIFF(HOUR,'2008-08-01 22:00:00','2008-08-01 23:01:00') ;
# +---------------------------------------------------------------+
# |TIMESTAMPDIFF(HOUR,'2008-08-01 22:00:00','2008-08-01 23:00:00')|
# +---------------------------------------------------------------+
# |1 |
# +---------------------------------------------------------------+
# # 差几分钟时
select TIMESTAMPDIFF(MINUTE,'2008-08-01 22:00:00','2008-08-01 23:01:00') ;
# +-----------------------------------------------------------------+
# |TIMESTAMPDIFF(MINUTE,'2008-08-01 22:00:00','2008-08-01 23:01:00')|
# +-----------------------------------------------------------------+
# |61 |
# +-----------------------------------------------------------------+1. 加几天 dateadd
# 加七天，不包括 created_time这天
select created_time ,
DATE_ADD(created_time, INTERVAL 7 DAY) from boys;
# +-------------------+--------------------------------------+
# |created_time |DATE_ADD(created_time, INTERVAL 7 DAY)|
# +-------------------+--------------------------------------+
# |1000-05-01 17:43:43|1000-05-08 17:43:43 |
# |1005-05-21 17:43:49|1005-05-28 17:43:49 |
# |1101-05-21 17:43:52|1101-05-28 17:43:52 |
# |1121-05-21 17:43:55|1121-05-28 17:43:55 |
# |1190-05-21 17:43:58|1190-05-28 17:43:58 |
# |2021-05-22 17:44:01|2021-05-29 17:44:01 |
# |2040-05-22 17:44:01|2040-05-29 17:44:01 |
# |2050-05-22 17:44:01|2050-05-29 17:44:01 |
# |1901-05-22 17:44:01|1901-05-29 17:44:01 |
# |1899-05-21 17:43:58|1899-05-28 17:43:58 |
# +-------------------+--------------------------------------+1. ifnull
update girls set is_deleted = '' where id = 4 ;
select is_deleted as is_deleted from girls;
# +----------+
# |is_deleted|
# +----------+
# |1 |
# |0 |
# |0 |
# | |
# +----------+
# is_deleted 这个字段如果是 null or ‘’ ,则赋值为 0
select ifnull(is_deleted,0) as is_deleted from girls;
# +----------+
# |is_deleted|
# +----------+
# |1 |
# |0 |
# |0 |
# |0 |
# +----------+
1. case when then else end
可以根据枚举值转文字；可以根据判断是否为null，null的话给默认值（类似Oracle 的isNul函数）；可以批量更新，这个可以解决一些奇奇怪怪的需求
eg：select case when 如果pc.taxNo字段不为空，显示pc.taxNo。如果pc.taxNo字段为空，显示'金额合计'。end结束
一：分析函数
# 由一个sql引入的分析函数 sum() over()
# 60
# 按照salary的累计和running_total，其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和，其他以此类推。
# 具体结果如下Demo展示。
CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
select * from salaries_60;
# +------+------+----------+----------+
# |emp_no|salary|from_date |to_date |
# +------+------+----------+----------+
# |1 |100 |2021-01-23|9999-01-01|
# |2 |1000 |2021-01-23|9999-01-01|
# |3 |10000 |2021-01-23|9999-01-01|
# |4 |500 |2021-01-23|9999-01-01|
# |5 |5000 |2021-01-23|9999-01-01|
# +------+------+----------+----------+
# running_total
select s.emp_no,
s.salary,
row_number() over (order by emp_no asc) as rn,
(select sum(s2.salary), row_number() over (order by emp_no asc) as rn2 from salaries_60 s2 where s2.rn2 <= s.rn) as running_total
from salaries_60 s;
# 识别不了别名 ，进行改写 （下面是个错误的sql）
select A.emp_no, A.salary, A.rn ,
(
select B.sm from (
select sum(s2.salary) as sm , row_number() over (order by emp_no asc) as rn2 from salaries_60 s2
) B where B.rn2 <= A.rn
) as running_total
from (
select s.emp_no,
s.salary,
row_number() over (order by emp_no asc) as rn
from salaries_60 s
) A ;
# +------+------+--+-------------+
# |emp_no|salary|rn|running_total|
# +------+------+--+-------------+
# |1 |100 |1 |16600 |
# |2 |1000 |2 |16600 |
# |3 |10000 |3 |16600 |
# |4 |500 |4 |16600 |
# |5 |5000 |5 |16600 |
# +------+------+--+-------------+
#避免 is incompatible with sql_mode=only_full_group_by 这个错误
set session sql_mode='';
select B.sm from (
select sum(s2.salary) as sm , row_number() over (order by emp_no asc) as rn2 from salaries_60 s2
) B where B.rn2 <= 3 ;
select sum(s2.salary) from salaries_60 s2;
# +--------------+
# |sum(s2.salary)|
# +--------------+
# |16600 |
# +--------------+
# 下面sql错误：sum(salary) 会对 salaries_60 表的所有数据求和，导致 B的 sm 是一个常量
select sm
from (
select sum(salary) sm, row_number() over (order by emp_no asc) rn2 from salaries_60 s2
) B
where B.rn2 <= 2;
# +-----+
# |sm |
# +-----+
# |16600|
# +-----+
# 正确解法
select sum(sm)
from (
select salary sm, row_number() over (order by emp_no asc) rn2 from salaries_60 s2
) B
# 识别不了 s2 ，所以加个套 ，where B.rn2 <= 2
where B.rn2 <= 2;
# +-------+
# |sum(sm)|
# +-------+
# |1100 |
# +-------+
# 正确sql
select A.emp_no, A.salary, A.rn ,
(
select sum(sm) from (
select s2.salary as sm , row_number() over (order by emp_no asc) as rn2 from salaries_60 s2
) B where B.rn2 <= A.rn
) as running_total
from (
select s.emp_no,
s.salary,
row_number() over (order by emp_no asc) as rn
from salaries_60 s
) A ;
# +------+------+--+-------------+
# |emp_no|salary|rn|running_total|
# +------+------+--+-------------+
# |1 |100 |1 |100 |
# |2 |1000 |2 |1100 |
# |3 |10000 |3 |11100 |
# |4 |500 |4 |11600 |
# |5 |5000 |5 |16600 |
# +------+------+--+-------------+
# 法二：(用分析函数)
SELECT emp_no, salary, SUM(salary) OVER (ORDER BY emp_no) AS running_total
FROM salaries
WHERE to_date = '9999-01-01';
# mysql的分析函数
# 分析函数之连续求和sum(…) over(…)
# 分析函数之排序值rank()和dense_rank()
# 分析函数之排序后顺序号row_number()
# 分析函数之取上下行数据lag()和lead()
# 分析函数和聚合函数的区别
# 普通的聚合函数用group by分组，每个分组返回一个统计值，
# 分析函数采用partition by分组，并且每组每行都可以返回一个统计值。
# 语法
# FUNCTION_NAME(<参数>,…)
# OVER (<PARTITION BY 表达式,…>
# <ORDER BY 表达式 <ASC DESC>
# <NULLS FIRST NULLS LAST>>
# <WINDOWING子句>)
# 分析函数带有一个开窗函数over()，包含三个分析子句:
#
# 分组(partition by)
# 排序(order by)
# 窗口(rows)
# 规则：
# sum(...) over( )，对所有行求和
#
# sum(...) over( order by ... )， 连续求和
#
#
# sum(...) over( partition by... )，同组内所行求和
#
# sum(...) over( partition by... order by ... )，同第1点中的排序求和原理，只是范围限制在组内
# 总结
# 在"… from emp;"后面不要加order by 子句，使用的分析函数的(partition by deptno order by sal)
# 里已经有排序的语句了，如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费劲了
# ① 连续求和
# 连续求和分析函数 sum(…) over(…)
# 【功能】连续求和分析函数
# 【参数】具体参示例
SELECT *
FROM salaries_60 s ;
# +------+------+----------+----------+---+
# |emp_no|salary|from_date |to_date |sex|
# +------+------+----------+----------+---+
# |1 |100 |2021-01-23|9999-01-01|0 |
# |2 |1000 |2021-01-23|9999-01-01|1 |
# |3 |10000 |2021-01-23|9999-01-01|1 |
# |4 |500 |2021-01-23|9999-01-01|0 |
# |5 |5000 |2021-01-23|9999-01-01|1 |
# +------+------+----------+----------+---+
# 对所有行求和
SELECT s.emp_no, s.salary , sum(s.salary) over() as so
FROM salaries_60 s ;
# +------+------+-----+
# |emp_no|salary|so |
# +------+------+-----+
# |1 |100 |16600|
# |2 |1000 |16600|
# |3 |10000 |16600|
# |4 |500 |16600|
# |5 |5000 |16600|
# +------+------+-----+
# 连续求和(连续的话就得加order by)
SELECT s.emp_no, s.salary , sum(s.salary) over(order by emp_no) as so
FROM salaries_60 s ;
# +------+------+-----+
# |emp_no|salary|so |
# +------+------+-----+
# |1 |100 |100 |
# |2 |1000 |1100 |
# |3 |10000 |11100|
# |4 |500 |11600|
# |5 |5000 |16600|
# +------+------+-----+
# group by 后，每个组 返回一个结果
SELECT sum(s.salary)
FROM salaries_60 s group by s.emp_no, s.salary;
# +-------------+
# |sum(s.salary)|
# +-------------+
# |100 |
# |1000 |
# |10000 |
# |500 |
# |5000 |
# +-------------+
# group by 后，每个组 返回一个结果
set session sql_mode='';
SELECT sum(s.salary)
FROM salaries_60 s group by s.salary;
# +-------------+
# |sum(s.salary)|
# +-------------+
# |100 |
# |1000 |
# |10000 |
# |500 |
# |5000 |
# +-------------+
SELECT sum(s.salary)
FROM salaries_60 s ;
# +-------------+
# |sum(s.salary)|
# +-------------+
# |16600 |
# +-------------+
# sum(sal) over (order by deptno,ename) 按emp_no 和 sex “连续”求总和
SELECT s.emp_no, s.salary , sum(s.salary) over(order by emp_no ,sex) as so
FROM salaries_60 s ;
# +------+------+-----+
# |emp_no|salary|so |
# +------+------+-----+
# |1 |100 |100 |
# |2 |1000 |1100 |
# |3 |10000 |11100|
# |4 |500 |11600|
# |5 |5000 |16600|
# +------+------+-----+
# 同组内求和
SELECT s.emp_no, s.salary , sum(s.salary) over(partition by sex ) as so
FROM salaries_60 s ;
# +------+------+-----+
# |emp_no|salary|so |
# +------+------+-----+
# |1 |100 |600 |
# |4 |500 |600 |
# |2 |1000 |16000|
# |3 |10000 |16000|
# |5 |5000 |16000|
# +------+------+-----+
# 同组内连续求和
# sum(...) over( partition by... order by ... )，同第1点中的排序求和原理，只是范围限制在组内
SELECT s.emp_no, s.salary , sum(s.salary) over(partition by sex order by emp_no) as so
FROM salaries_60 s ;
# +------+------+-----+
# |emp_no|salary|so |
# +------+------+-----+
# |1 |100 |100 |
# |4 |500 |600 |
# |2 |1000 |1000 |
# |3 |10000 |11000|
# |5 |5000 |16000|
# +------+------+-----+
# 即sex从大到小排列，sex组里各员工的薪水从高到低排列，累计和的规则不变（组内连续求和和 总体连续求和）。
SELECT
s.emp_no,
s.salary ,
sum(s.salary) over(partition by sex order by salary desc) as sex_gro_sum ,
sum(s.salary) over(order by salary desc) as all_sum
FROM salaries_60 s ;
# +------+------+-----------+-------+
# |emp_no|salary|sex_gro_sum|all_sum|
# +------+------+-----------+-------+
# |3 |10000 |10000 |10000 |
# |5 |5000 |15000 |15000 |
# |2 |1000 |16000 |16000 |
# |4 |500 |500 |16500 |
# |1 |100 |600 |16600 |
# +------+------+-----------+-------+
SELECT
s.emp_no,
s.salary ,
sum(s.salary) over(partition by sex order by emp_no asc ,salary desc) as sex_gro_sum ,
sum(s.salary) over(order by emp_no asc ,salary desc ) as all_sum
FROM salaries_60 s ;
# +------+------+-----------+-------+
# |emp_no|salary|sex_gro_sum|all_sum|
# +------+------+-----------+-------+
# |1 |100 |100 |100 |
# |2 |1000 |1000 |1100 |
# |3 |10000 |11000 |11100 |
# |4 |500 |600 |11600 |
# |5 |5000 |16000 |16600 |
# +------+------+-----------+-------+
一：
w3school：SQL MID() 函数
一.
WITH as ①做子查询
WITH recursion_name (name, short_name, code, parent_code, level, flag) AS
(SELECT T1.name, T1.short_name, T1.code, T1.parent_code, T1.level, T1.flag
from sys_region T1
where T1.code = '370000000000')
select *
from recursion_name;②sql 递归，生成一行一行数据集，最后用 union all 拼成一个大的结果集
-- 查询子节点 不含自己 (recursion 递归)
WITH RECURSIVE recursion (name, short_name, code, parent_code, level, flag) AS
(
SELECT T1.name, T1.short_name, T1.code, T1.parent_code, T1.level, T1.flag
from sys_region T1
where T1.code='370000000000'
UNION ALL
SELECT T2.name, T2.short_name, T2.code, T2.parent_code, T2.level, T2.flag
from sys_region T2,
recursion T3
WHERE T2.parent_code=T3.code
)
SELECT T.name, T.short_name, T.code, T.parent_code, T.level, T.flag
FROM recursion T
where T.code!='370000000000' ;分析：处理流程：
①
union all 上面的sql
SELECT T1.id, T1.name, T1.short_name, T1.code, T1.parent_code, T1.level, T1.flag
from sys_region T1
where T1.code='370171401000' +---+----+----------+------------+------------+-----+----+
|id |name|short_name|code |parent_code |level|flag|
+---+----+----------+------------+------------+-----+----+
|181|章锦街道|章锦街道 |370171401000|370171000000|4 |0 |
+---+----+----------+------------+------------+-----+----+是最开始要处理的数据集
② union all 下面的sql 是将 ① 中生成的结果 逐条遍历
例如第一条：
SELECT T2.id, T2.name, T2.short_name, T2.code, T2.parent_code, T2.level, T2.flag
from sys_region T2, recursion T3
WHERE T2.code= “370171000000”③ 执行Union all 和 之前的原始 sql 拼接成 两条记录
④ 然后刚刚生成的 新记录调用 回调函数 recursion ，执行 union all 下的sql 方法，得到的结果集再去 union all
⑤ 每生成新的记录就会 调用 recursion，直到不会产生新的记录
结果集为：
+--+-----------+----------+------------+------------+-----+----+
|id|name |short_name|code |parent_code |level|flag|
+--+-----------+----------+------------+------------+-----+----+
|16|济南高新技术产业开发区|高新区 |370171000000|370100000000|3 |0 |
+--+-----------+----------+------------+------------+-----+----+
1. with as 多个 临时表： with 临时表A as （select 子查询1），临时表B as (select 子查2 可以用到临时表A) select * from 临时表B inner |left | right join on
with A as (
select boys.id as id, boys.name, boys.sex, boys.age from boys inner join girl g on boys.id = g.boy_id
),
B as (
select A.id, A.name, A.sex, A.age from A where A.id = 3
)
select B.* from B ;查询结果如下：
+--+----+---+---+
|id|name|sex|age|
+--+----+---+---+
|3 |慕容垂 |男 |44 |
|3 |慕容垂 |男 |44 |
|3 |慕容垂 |男 |44 |
|3 |慕容垂 |男 |44 |
|3 |慕容垂 |男 |44 |
+--+----+---+---+
一.
DATEDIFF()
# DATEDIFF() 函数计算两个日期之间的间隔天数
SELECT DATEDIFF('2017-11-10','2017-11-29') AS COL1;
# +----+
# |COL1|
# +----+
# |-19 |
# +----+
SELECT DATEDIFF('2017-11-29','2017-11-20') AS COL1;
# +----+
# |COL1|
# +----+
# |9 |
# +----+一.
# DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
# DATE_FORMAT(date,format)函数则是把数据库的日期转换为对应的字符串格式
# 语法： DATE_FORMAT(date,format)
select DATE_FORMAT(NOW(),'%Y-%m-%d') as date_format;
# +-----------+
# |date_format|
# +-----------+
# |2021-01-16 |
# +-----------+
select DATE_FORMAT(NOW(),'%Y年%m月%d日') as date_format;
# +-----------+
# |date_format|
# +-----------+
# |2021年01月16日|
# +-----------+
# STR_TO_DATE(str,format)函数是将时间格式的字符串（str），按照所提供的显示格式（format）转换为DATETIME类型的值
SELECT STR_TO_DATE('2021-01-16 10:20:30','%Y-%m-%d %H:%i:%s') AS result;
# +-------------------+
# |result |
# +-------------------+
# |2021-01-16 10:20:30|
# +-------------------+
SELECT STR_TO_DATE('2021-01-16 10:20:30','%Y-%m-%d') AS result;
# +----------+
# |result |
# +----------+
# |2021-01-16|
# +----------+
# MID() 函数
# MID 函数用于从文本字段中提取字符
# 语法：
# SELECT MID(column_name,start[,length]) FROM table_name
# start 必需。规定开始位置（起始值是 1）。
# length 要返回的字符数。如果省略，则 MID() 函数返回剩余文本。
select mid(name,1) from commodity_order where id = 1;
# +-----------+
# |mid(name,1)|
# +-----------+
# |置办年货 |
# +-----------+
select mid(name,1,1) from commodity_order where id = 1;
# +-------------+
# |mid(name,1,1)|
# +-------------+
# |置 |
# +-------------+
select mid(name,1,3) from commodity_order where id = 1;
# +-------------+
# |mid(name,1,3)|
# +-------------+
# |置办年 |
# +-------------+
# length() 函数
# length 函数返回文本字段中值的长度。
# 语法：SELECT LEN(column_name) FROM table_name
select length(name) from commodity_order where id = 1;
# +------------+
# |length(name)|
# +------------+
# |12 |
# +------------+
# substr 从 1 开始截取[index,截取长度
select substr(name,1) from commodity_order where id = 1;
# +--------------+
# |substr(name,1)|
# +--------------+
# |置办年货 |
# +--------------+
select substr(name,2) from commodity_order where id = 1;
# +--------------+
# |substr(name,2)|
# +--------------+
# |办年货 |
# +--------------+
select substr(name,2,2) from commodity_order where id = 1;
# +----------------+
# |substr(name,2,2)|
# +----------------+
# |办年 |
# +----------------+
# LEFT()函数是一个字符串函数，它返回具有指定长度的字符串的左边部分。
# LEFT(str,length);
select LEFT(name,2) from commodity_order where id = 1;
#+------------+
# |LEFT(name,2)|
# +------------+
# |置办 |
# +------------+
一：
length()
replace()
# 9
select length('郭一光') ;
# 3
select length('gyg') ;
# 6
select length('10,A,B') ;
# 4
select length(replace('10,A,B',',',''));
# 2 统计 10,A,B 出现 , 的次数
select (length('10,A,B')) - length(replace('10,A,B',',','')) ;一.
GROUP_CONCAT（）函数
GROUP_CONCAT函数返回一个字符串结果，该结果由分组中的值连接组合而成。
常用用法：
MySQL 将查询出来的一列数据拼装成一个字符串（纵向拼接，而concat是横向拼接）
1、使用语法及特点：
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]
[SEPARATOR str_val])
在 MySQL 中，你可以得到表达式结合体的连结值。通过使用 DISTINCT 可以排除重复值。如果希望对结果中的值进行排序，可以使用 ORDER BY 子句。
SEPARATOR 是一个字符串值，它被用于插入到结果值中。缺省为一个逗号 (",")，可以通过指定 SEPARATOR "" 完全地移除这个分隔符。
可以通过变量 group_concat_max_len 设置一个最大的长度。在运行时执行的句法如下： SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer;
如果最大长度被设置，结果值被剪切到这个最大长度。如果分组的字符过长，可以对系统参数进行设置：SET @@global.group_concat_max_len=40000;
+----------+----+--------------------------+
| locus | id | journal |
+----------+----+--------------------------+
| AB086827 | 1 | Unpublished |
| AB086827 | 2 | Submitted (20-JUN-2002) |
| AF040764 | 23 | Unpublished |
| AF040764 | 24 | Submitted (31-DEC-1997) |
+----------+----+--------------------------+
语句 SELECT locus,GROUP_CONCAT(id) FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus; 的返回结果为
+----------+------------------+
| locus | GROUP_CONCAT(id) |
+----------+------------------+
| AB086827 | 1,2 |
| AF040764 | 23,24 |
+----------+------------------+
语句 SELECT locus,GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') FROM info WHERE locus IN('AB086827','AF040764') GROUP BY locus;的返回结果为
+----------+----------------------------------------------------------+
| locus | GROUP_CONCAT(distinct id ORDER BY id DESC SEPARATOR '_') |
+----------+----------------------------------------------------------+
| AB086827 | 2_1 |
| AF040764 | 24_23 |
+----------+----------------------------------------------------------+
纵向拼接：
1. 对数据分组后，获取组内排名前几的记录
思路①:rank() over() 推荐：分区后对组内的数据进行排序
rank() over(partition by cid order by score desc) 可能取多
dense_rank() over(partition by cid order by score desc) 可能取多
row_number() over (partition by cid order by score desc) 推荐
思路②：不用group by 后的分组，每条记录在 组内的排名（子查询）
代码如下：
# 元数据如下：
select cid, score
from sc B
# group by cid, score # 后会有两条数据被处理掉，这两条数据 cid和score 一样，但是 sid 所属同学是不同的
order by cid, score desc;
# +---+-----+
# |cid|score|
# +---+-----+
# |01 |80.0 |
# |01 |80.0 |
# |01 |76.0 |
# |01 |70.0 |
# |01 |50.0 |
# |01 |31.0 |
# |02 |90.0 |
# |02 |89.0 |
# |02 |87.0 |
# |02 |80.0 |
# |02 |60.0 |
# |02 |30.0 |
# |03 |99.0 |
# |03 |98.0 |
# |03 |80.0 |
# |03 |80.0 |
# |03 |34.0 |
# |03 |20.0 |
# +---+-----+
# 查询各科成绩前三名的记录
# rank() over(partition by cid order by score desc) 对数据分区后，然后对分区后的数据进行排序
# 参考：（必看）
# 思路①：分区内排序
select *, rank() over(partition by cid order by score desc) as graderank from sc;
# 18 条数据
# +---+---+-----+---------+
# |Sid|Cid|score|graderank|
# +---+---+-----+---------+
# |01 |01 |80.0 |1 |
# |03 |01 |80.0 |1 |
# |05 |01 |76.0 |3 |
# |02 |01 |70.0 |4 |
# |04 |01 |50.0 |5 |
# |06 |01 |31.0 |6 |
# |01 |02 |90.0 |1 |
# |07 |02 |89.0 |2 |
# |05 |02 |87.0 |3 |
# |03 |02 |80.0 |4 |
# |02 |02 |60.0 |5 |
# |04 |02 |30.0 |6 |
# |01 |03 |99.0 |1 |
# |07 |03 |98.0 |2 |
# |02 |03 |80.0 |3 |
# |03 |03 |80.0 |3 |
# |06 |03 |34.0 |5 |
# |04 |03 |20.0 |6 |
# +---+---+-----+---------+
select *, dense_rank() over(partition by cid order by score desc) as graderank from sc;
# 18条数据
# +---+---+-----+---------+
# |Sid|Cid|score|graderank|
# +---+---+-----+---------+
# |01 |01 |80.0 |1 |
# |03 |01 |80.0 |1 |
# |05 |01 |76.0 |2 |
# |02 |01 |70.0 |3 |
# |04 |01 |50.0 |4 |
# |06 |01 |31.0 |5 |
# |01 |02 |90.0 |1 |
# |07 |02 |89.0 |2 |
# |05 |02 |87.0 |3 |
# |03 |02 |80.0 |4 |
# |02 |02 |60.0 |5 |
# |04 |02 |30.0 |6 |
# |01 |03 |99.0 |1 |
# |07 |03 |98.0 |2 |
# |02 |03 |80.0 |3 |
# |03 |03 |80.0 |3 |
# |06 |03 |34.0 |4 |
# |04 |03 |20.0 |5 |
# +---+---+-----+---------+
select *, row_number() over (partition by cid order by score desc) as graderank from sc;
# +---+---+-----+---------+
# |Sid|Cid|score|graderank|
# +---+---+-----+---------+
# |01 |01 |80.0 |1 |
# |03 |01 |80.0 |2 |
# |05 |01 |76.0 |3 |
# |02 |01 |70.0 |4 |
# |04 |01 |50.0 |5 |
# |06 |01 |31.0 |6 |
# |01 |02 |90.0 |1 |
# |07 |02 |89.0 |2 |
# |05 |02 |87.0 |3 |
# |03 |02 |80.0 |4 |
# |02 |02 |60.0 |5 |
# |04 |02 |30.0 |6 |
# |01 |03 |99.0 |1 |
# |07 |03 |98.0 |2 |
# |02 |03 |80.0 |3 |
# |03 |03 |80.0 |4 |
# |06 |03 |34.0 |5 |
# |04 |03 |20.0 |6 |
# +---+---+-----+---------+
# graderank 表中没有字段，解析不到，所以需要加个 套，套起这个sql
select * from (select *, rank() over(partition by cid order by score desc) as graderank from sc) A
where A.graderank <= 3 ;
# 10 条数据
# +---+---+-----+---------+
# |Sid|Cid|score|graderank|
# +---+---+-----+---------+
# |01 |01 |80.0 |1 |
# |03 |01 |80.0 |1 |
# |05 |01 |76.0 |3 |
# |01 |02 |90.0 |1 |
# |07 |02 |89.0 |2 |
# |05 |02 |87.0 |3 |
# |01 |03 |99.0 |1 |
# |07 |03 |98.0 |2 |
# |02 |03 |80.0 |3 |
# |03 |03 |80.0 |3 |
# +---+---+-----+---------+
select * from (select *, dense_rank() over(partition by cid order by score desc) as graderank from sc) A
where A.graderank <= 3 ;
# 11 条数据
# +---+---+-----+---------+
# |Sid|Cid|score|graderank|
# +---+---+-----+---------+
# |01 |01 |80.0 |1 |
# |03 |01 |80.0 |1 |
# |05 |01 |76.0 |2 |
# |02 |01 |70.0 |3 |
# |01 |02 |90.0 |1 |
# |07 |02 |89.0 |2 |
# |05 |02 |87.0 |3 |
# |01 |03 |99.0 |1 |
# |07 |03 |98.0 |2 |
# |02 |03 |80.0 |3 |
# |03 |03 |80.0 |3 |
# +---+---+-----+---------+
#
select * from (select *, row_number() over(partition by cid order by score desc) as graderank from sc) A
where A.graderank <= 3 ;
# +---+---+-----+---------+
# |Sid|Cid|score|graderank|
# +---+---+-----+---------+
# |01 |01 |80.0 |1 |
# |03 |01 |80.0 |2 |
# |05 |01 |76.0 |3 |
# |01 |02 |90.0 |1 |
# |07 |02 |89.0 |2 |
# |05 |02 |87.0 |3 |
# |01 |03 |99.0 |1 |
# |07 |03 |98.0 |2 |
# |02 |03 |80.0 |3 |
# +---+---+-----+---------+
# 思路②
# mysql 用Group by分组后,取每组的前几条记录的方法和理解
# 参考链接：
# https://blog.csdn.net/junzi528/article/details/84404412?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control
# 转化思路：相同课程下，该同学的排名，只取第一名，第二名和第三名的
# 下面sql 语法错误： 解析不了 rk 列，需要加个 套
# select
# Cid,
# score ,
# (select count(1) from sc B where B.Cid = A.Cid and B.score >= A.score) as rk
#
# from sc A
# where rk <=2 ;
select *
from (
select Cid,
score,
(select count(1) from sc B where B.Cid = A.Cid and B.score >= A.score) as rk
from sc A
) RK
where rk <= 2 order by cid;
# 结果如下：
# +---+-----+--+
# |Cid|score|rk|
# +---+-----+--+
# |01 |80.0 |2 |
# |01 |80.0 |2 |
# |02 |90.0 |1 |
# |02 |89.0 |2 |
# |03 |99.0 |1 |
# |03 |98.0 |2 |
# +---+-----+--+
# 另一种写法：思路是一样的
select Cid,
score,
# 同一课程下这个分数的名次
(select count(1) from sc B where B.Cid = A.Cid and B.score >= A.score) as Rk
from sc A
where (select count(1) from sc B where B.Cid = A.Cid and B.score >= A.score) <= 2
order by cid;
# +---+-----+--+
# |Cid|score|Rk|
# +---+-----+--+
# |01 |80.0 |2 |
# |01 |80.0 |2 |
# |02 |90.0 |1 |
# |02 |89.0 |2 |
# |03 |99.0 |1 |
# |03 |98.0 |2 |
# +---+-----+--+
# 改进sql如下：
select Cid,
score
from sc A
where (select count(1) from sc B where B.Cid = A.Cid and B.score >= A.score) <= 2
order by cid;
# 结果如下：
# +---+-----+
# |Cid|score|
# +---+-----+
# |01 |80.0 |
# |01 |80.0 |
# |02 |90.0 |
# |02 |89.0 |
# |03 |99.0 |
# |03 |98.0 |
# +---+-----+
# group by
select cid ,score from sc group by cid,score ;
# group by 可以取到各个组的最大值,cid,score 这个分组
select cid ,score, max(score) as max from sc group by cid,score;
# 结果如下：
# +---+-----+----+
# |cid|score|max |
# +---+-----+----+
# |01 |80.0 |80.0|
# |02 |90.0 |90.0|
# |03 |99.0 |99.0|
# |01 |70.0 |70.0|
# |02 |60.0 |60.0|
# |03 |80.0 |80.0|
# |02 |80.0 |80.0|
# |01 |50.0 |50.0|
# |02 |30.0 |30.0|
# |03 |20.0 |20.0|
# |01 |76.0 |76.0|
# |02 |87.0 |87.0|
# |01 |31.0 |31.0|
# |03 |34.0 |34.0|
# |02 |89.0 |89.0|
# |03 |98.0 |98.0|
# +---+-----+----+
select cid ,max(score) as max from sc group by cid;
# 结果如下：
# +---+----+
# |cid|max |
# +---+----+
# |01 |80.0|
# |02 |90.0|
# |03 |99.0|
# +---+----+
# 下面sql 语法错误 因为 A.score < max(B.score) max函数位置错误，所以考虑用子查询代替
# select B.cid,
# max(B.score) as max,
# (select max(A.score) from sc A where A.Cid = B.cid and A.score < max(B.score)) as second
# from sc B
# group by B.cid;
# 注意：下面sql 设计有问题：课程1 的 80 分有两个同学，下面的sql 是按照一个处理的
# 修改完用子查询 代替 max的sql如下：
select B.cid,
max(B.score) as max,
# 第二就是除了max后的最大值，所以先用 max函数，然后限制条件 加上 < max(B.score)
(select max(A.score)
from sc A
# 限制同一个课程
where A.Cid = B.cid
and A.score < (select max(c.score)
from sc c
where c.Cid = a.cId)) as second
from sc B
group by B.cid;
# 查询结果
# +---+----+------+
# |cid|max |second|
# +---+----+------+
# |01 |80.0|76.0 |
# |02 |90.0|89.0 |
# |03 |99.0|98.0 |
# +---+----+------+
# 说明:如果取分组后，组内的前三条数据就比较麻烦了
select B.cid,
max(B.score) as max,
# 第二就是除了max后的最大值，所以先用 max函数，然后限制条件 加上 < max(B.score)
(select max(A.score)
from sc A
# 限制同一个课程
where A.Cid = B.cid
and A.score < (select max(c.score)
from sc c
where c.Cid = a.cId)) as second,
# 第三
(select max(A.score)
from sc A
# 限制同一个课程
where A.Cid = B.cid
# 取出分组后，组内的第二大
and A.score < (select min(score)
from (
select score
from sc d
where d.Cid = B.cid
group by score
order by score desc
limit 2
) secondTable)) as third
from sc B
group by B.cid;
# 结果如下：
# +---+----+------+-----+
# |cid|max |second|third|
# +---+----+------+-----+
# |01 |80.0|76.0 |70.0 |
# |02 |90.0|89.0 |87.0 |
# |03 |99.0|98.0 |80.0 |
# +---+----+------+-----+
# 上例中 获取第二的sql,最后将 01 替换为 B.cid
select min(score)
from (
select score
from sc d
where d.Cid = 01
group by score
order by score desc
limit 2
) secondTable;
1. rank（），dense_rank（），rownum（）函数用来分区后组内排序
# 说明： rank() 函数，可以给衍生表增加一列，该列代表顺序,ps:不会改变行数
select *, rank() over(partition by cid order by score desc) as graderank from sc ;
# 结果如下：
# +---+---+-----+---------+
# |Sid|Cid|score|graderank|
# +---+---+-----+---------+
# |01 |01 |80.0 |1 |
# |03 |01 |80.0 |1 |
# |05 |01 |76.0 |3 |
# |02 |01 |70.0 |4 |
# |04 |01 |50.0 |5 |
# |06 |01 |31.0 |6 |
# |01 |02 |90.0 |1 |
# |07 |02 |89.0 |2 |
# |05 |02 |87.0 |3 |
# |03 |02 |80.0 |4 |
# |02 |02 |60.0 |5 |
# |04 |02 |30.0 |6 |
# |01 |03 |99.0 |1 |
# |07 |03 |98.0 |2 |
# |02 |03 |80.0 |3 |
# |03 |03 |80.0 |3 |
# |06 |03 |34.0 |5 |
# |04 |03 |20.0 |6 |
# +---+---+-----+---------+
select *, rank() over( order by score desc) as graderank from sc ;
# 结果如下：
# +---+---+-----+---------+
# |Sid|Cid|score|graderank|
# +---+---+-----+---------+
# |01 |03 |99.0 |1 |
# |07 |03 |98.0 |2 |
# |01 |02 |90.0 |3 |
# |07 |02 |89.0 |4 |
# |05 |02 |87.0 |5 |
# |01 |01 |80.0 |6 |
# |02 |03 |80.0 |6 |
# |03 |01 |80.0 |6 |
# |03 |02 |80.0 |6 |
# |03 |03 |80.0 |6 |
# |05 |01 |76.0 |11 |
# |02 |01 |70.0 |12 |
# |02 |02 |60.0 |13 |
# |04 |01 |50.0 |14 |
# |06 |03 |34.0 |15 |
# |06 |01 |31.0 |16 |
# |04 |02 |30.0 |17 |
# |04 |03 |20.0 |18 |
# +---+---+-----+---------+
select Count(*) from sc ;
# +--------+
# |Count(*)|
# +--------+
# |18 |
# +--------+
# 测试
create table students_rank(
id int(4) auto_increment primary key,
name varchar(50) not null,
score int(4) not null
);
insert into students_rank(name,score) values('curry', 100),
('klay', 99),
('KD', 100),
('green', 90),
('James', 99),
('AD', 96);
select * from students_rank;
# 语法错误
# select id, name,score , rank() from students_rank;
# 使用三种不同的方法排序
# 排序 常规写法：
select id, name,score from students_rank order by score desc ;
# 结果如下：
# +--+-----+-----
# |id|name |score
# +--+-----+-----
# |1 |curry|100
# |3 |KD |100
# |2 |klay |99
# |5 |James|99
# |6 |AD |96
# |4 |green|90
# +--+-----+-----
select id, name,score, rank() over(order by score desc) as r from students_rank;
# 结果如下：
# +--+-----+-----+-+
# |id|name |score|r|
# +--+-----+-----+-+
# |1 |curry|100 |1|
# |3 |KD |100 |1|
# |2 |klay |99 |3|
# |5 |James|99 |3|
# |6 |AD |96 |5|
# |4 |green|90 |6|
# +--+-----+-----+-+
# DENSE :名次之间是连续的整数值 ，名词之间不能有断层
select id, name, score,DENSE_RANK() OVER(order by score desc) as dense_r from students_rank;
#结果如下：
# +--+-----+-----+-------+
# |id|name |score|dense_r|
# +--+-----+-----+-------+
# |1 |curry|100 |1 |
# |3 |KD |100 |1 |
# |2 |klay |99 |2 |
# |5 |James|99 |2 |
# |6 |AD |96 |3 |
# |4 |green|90 |4 |
# +--+-----+-----+-------+
select id, name,score, row_number() OVER(order by score desc) as row_r from students_rank;
# 结果如下
# +--+-----+-----+-----+
# |id|name |score|row_r|
# +--+-----+-----+-----+
# |1 |curry|100 |1 |
# |3 |KD |100 |2 |
# |2 |klay |99 |3 |
# |5 |James|99 |4 |
# |6 |AD |96 |5 |
# |4 |green|90 |6 |
# +--+-----+-----+-----+
174
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
