libin's个人网站3.0-SQL进阶

当前位置：首页 >> SQL >> SQL进阶

SQL进阶

2020-07-04 21:34:25 星期六阅读：2417

![](/static/images/article_images/1693752154.109906.jpeg)

本文介绍一些SQL中常用且小众的语法

####case 语句优化
```
select 
	case when a.id between 1 and 3 then "1" 
		when a.id between 4 and 6 then "2"
		when a.id between 7 and 9 then "3"
		else "unknow"  end ,
		sum(a.num) 
from test_table 
group by 
case when a.id between 1 and 3 then "1" 
		when a.id between 4 and 6 then "2"
		when a.id between 7 and 9 then "3"
		else "unknow"  end 
```

优化结果如下,group by 后面直接跟字段别名
```
select 
	case when a.id between 1 and 3 then "1" 
		when a.id between 4 and 6 then "2"
		when a.id between 7 and 9 then "3"
		else "unknow"  end as id_type ,
		sum(a.num) 
from test_table 
group by id_type 
```

####求表中不同列之间的最大值
比如：某个表有x,y,z三个字段，求每一行这三个字段的最大值
```
# 方法一：
select t.id  , greatest(greatest(x,y),z) as max_value 
from  test_table t

# 方法二：
SELECT  t.id, greatest(x, y, z)  FROM test t
```

####用MySQL实现row_number
```
select 
id ,date , price , 
(select count(*) from test_table 
where date = t.date and t.price < price ) + 1 as rn
from test_table t 
order by rn  asc 
having rn = 2 
```
用row_number()函数实现如下
```
select a.* 
from (
select *  ,  row_number() over(partition date order by price desc ) rn 
from test_table ) a 
where a.rn = 2 
```
####not exists的用法
求所有科目都在80分以上的学生。
解析：可以理解为“没有一科分数小于80分”，可以把小于80分的学生排除掉，就是满足条件的。
```
#第一种方法
select  distinct t.student_id 
from testScores t
where not exists(select * from testScores where student_id = t.student_id and score < 80 )

#第二种方法
select  student_id , 
count(distinct subject_) total_num  , 
 sum(case when score > 80 then 1 else 0 end )  cur_num 
from testScores 
group by student_id 
having total_num = cur_num 
```

####IN与EXISTE的性能比较
需求：从emp1中查出同时存在emp2表中的员工
使用IN的方法：
`select * from emp1 where id in (select id from emp2)`
使用exists的方法：
`select * from emp1 where exists(select * from emp2 where id = emp1.id)`
使用in时，子查询会生成一张临时表（内联视图），外层的SQL需要全表扫描该视图，非常耗费资源。
使用exists时，不会生成临时表。如果ID是索引，关联起来速度非常快。

结论：
①、数据量大时，exists的性能比in好。数据量小时，两者性能差不多。
②、使用in时，代码的可以读性好，容易理解。使用exists时，代码可读性差。

####根据某两个字段去重
```
SELECT * FROM test GROUP BY add_time ,name ; 
```
如果执行过程中报错，需要设置MySQL的全局变量，执行如下语句后再重启MySQL。
```
set @@global.sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION ";
```

####自定义排序
根据表里的时间字段倒序排序，且时间为null的排在最前面
```
select  * 
from test 
order by  case when add_time is null then  1 else 0 end desc , add_time desc 
```

####MySQL分列
需求：某个表的measuring_dimension字段是按照逗号分隔的字符串，现在通过substring_index函数进行分列。
```
SELECT
  id,
  measuring_dimension,
  substring_index(measuring_dimension, ",", 1)                            column1,
  substring_index(substring_index(measuring_dimension, ",", 2), ",", -1)  column2,
  substring_index(substring_index(measuring_dimension, ",", 3), ",", -1)  column3,
  substring_index(substring_index(measuring_dimension, ",", 4), ",", -1)  column4,
  substring_index(substring_index(measuring_dimension, ",", 5), ",", -1)  column5,
  substring_index(substring_index(measuring_dimension, ",", 6), ",", -1)  column6,
  substring_index(substring_index(measuring_dimension, ",", 7), ",", -1)  column7,
  substring_index(substring_index(measuring_dimension, ",", 8), ",", -1)  column8,
  substring_index(substring_index(measuring_dimension, ",", 9), ",", -1)  column9,
  substring_index(substring_index(measuring_dimension, ",", 10), ",", -1) column10,
  substring_index(substring_index(measuring_dimension, ",", 11), ",", -1) column11,
  substring_index(substring_index(measuring_dimension, ",", 12), ",", -1) column12
FROM config_size_measuring
```

当前位置： 首页 >> SQL >> SQL进阶

SQL进阶

技术交流 问题反馈

当前位置：首页 >> SQL >> SQL进阶

技术交流
问题反馈