Mysql项目上的一些应用

explain

可以知晓MySQL执行该SELECT语句时是否使用了索引、全表扫描、临时表、排序等信息。尽量避免MySQL进行全表扫描、使用临时表、排序等

EXPLAIN sql

https://zhuanlan.zhihu.com/p/149807046

行列转换

https://www.cnblogs.com/weibanggang/p/9679301.html

https://www.jianshu.com/p/5a2dae144238

行转列

create table t_score(
id int primary key auto_increment,
name varchar(20) not null,  #名字
Subject varchar(10) not null, #科目
Fraction double default 0  #分数
);


INSERT INTO `t_score`(name,Subject,Fraction) VALUES
('王海', '语文', 86),
('王海', '数学', 83),
('王海', '英语', 93),
('陶俊', '语文', 88),
('陶俊', '数学', 84),
('陶俊', '英语', 94),
('刘可', '语文', 80),
('刘可', '数学', 86),
('刘可', '英语', 88),
('李春', '语文', 89),
('李春', '数学', 80),
('李春', '英语', 87);

SELECT * FROM t_score;


select name as 名字 ,
sum(if(Subject='语文',Fraction,0)) as 语文,
sum(if(Subject='数学',Fraction,0))as 数学,
sum(if(Subject='英语',Fraction,0))as 英语,
round(AVG(Fraction),2) as 平均分,
SUM(Fraction) as 总分
from t_score group by name



SELECT name ,
    MAX(CASE Subject WHEN '数学' THEN Fraction ELSE 0 END ) 数学,
    MAX(CASE Subject WHEN '语文' THEN Fraction ELSE 0 END ) 语文,
    MAX(CASE Subject WHEN '英语' THEN Fraction ELSE 0 END ) 英语
FROM t_score
GROUP BY name;

with rollup 不能在5.7以下用

select
ifnull(name,'TOTAL') name,
sum(if(Subject='语文',Fraction,0)) as 语文,
sum(if(Subject='数学',Fraction,0))as 数学,
sum(if(Subject='英语',Fraction,0))as 英语,
round(AVG(Fraction),2) as 平均分,
SUM(Fraction) as 总分
from t_score group by name WITH ROLLUP

列转行

SELECT * FROM
(
 SELECT name,'chinese' AS Course,chinese FROM t_score2
 UNION ALL
 SELECT Name,'math' AS Course,math  FROM t_score2
 UNION ALL
 SELECT Name,'english' AS Course,english FROM t_score2
) t
ORDER BY name
;

字符串拆分

https://www.jb51.net/article/206106.htm

前提是有这个表mysql.help_topic ，此处利用 mysql 库的 help_topic 表的 help_topic_id 来作为变量，因为 help_topic_id 是自增的，当然也可以用其他表的自增字段辅助。

SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7782',',',help_topic_id+1),',',-1) AS num
FROM
    mysql.help_topic
WHERE
    help_topic_id < LENGTH('7654,7698,7782,7782')-LENGTH(REPLACE('7654,7698,7782,7782',',',''))+1

项目的一个例子表fe_self_add是自行加的id从1开始，product_series以逗号间隔的字段AC,AXP,MZ,XR,XTAL

select a.login_name ,
    SUBSTRING_INDEX(SUBSTRING_INDEX(a.product_series, ',', b.id), ',',-1) as product_series
from fe_user_proseries a
inner join fe_self_add b
    on
b.id < (length(a.product_series) - length(replace(a.product_series, ',', '')) + 2)

显示查询序列号

SELECT (@rownum:=@rownum+1) AS  rownum,name FROM  (select @rownum:=0) r,tab_user

5.7ROW_NUMBER窗口函数

http://www.136.la/nginx/show-120873.html

https://www.cnblogs.com/Gin-23333/p/5630720.html

下面的方法注意SELECT * FROM test_rownumber ORDER BY name,num DESC

排序的字段需要连在一起才能计算rn

SELECT * FROM
(
SELECT
-- 当变量@name等于字段值的时候，变量@rn加1，如果不相等赋值为 1
@rn := CASE WHEN @name = NAME THEN @rn + 1 ELSE 1 END AS rn ,
-- 把name字段值赋值于变量@name
@name:=name as name1 ,
num
FROM
( SELECT * FROM test_rownumber ORDER BY name,num DESC ) a,
-- 初始化一个变量值
(SELECT @rn := 0,@name:='') b
) c

项目一个例子

credite_name_list表有多个registration_date的数据，ORDER BY ymt_account_number, registration_date DESC

根据时间倒序取数

SELECT c.* FROM
    (
        SELECT
        @rn := CASE WHEN @name = ymt_account_number THEN @rn + 1 ELSE 1 END AS rn ,
        @name:= ymt_account_number as  ymt_account_number,
         account_holder,holder_type ,id_number ,ordinary_account_number
        FROM
        (
            SELECT DISTINCT ymt_account_number,REPLACE(account_holder,'#','') account_holder,holder_type ,
            id_number ,ordinary_account_number
            FROM  credite_name_list
            WHERE date_format(registration_date,'%Y年%m月') ='2021年06月'
            ORDER BY ymt_account_number, registration_date DESC
        ) a,
        (SELECT @rn := 0,@name:='') b
) c
WHERE   c.rn = 1