mysql 笔记

• 行转列

 

name	course	grade
zhangsan	java	20
zhangsan	c#	60
zhangsan	python	40
lisi	java	109
lisi	c#	30
lisi	python	20
wangwu	java	33

 

select name ,
sum(case when course = 'java' then grade end) as 'java',
sum(case when course = 'c#' then grade end) as 'c#',
sum(case when course = 'python' then grade end) as 'python'
from ccc GROUP BY name;

 

name	java	c#	python
zhangsan	20	60	40
lisi	109	30	20
wangwu	33

 

• 字符串拼接

　　CONCAT(str1,str2,...)：参数中出现null，则结果返回null

select CONCAT(11,22,33,'hj')    

CONCAT(11,22,33,'hj')

112233hj

---

 select CONCAT(11,22,33,'hj',null)     

CONCAT(11,22,33,'hj',null)

null

 

---

　　CONCAT_WS(separator,str1,str2,...)： 1.可以指定拼接符号  2.返回结果忽略null

 select CONCAT_WS('|',11,22,33)

CONCAT_WS('|',11,22,33,null)

11|22|33

---

 

• 分组排序

rank() over(PARTITION by *** order by ***) 

id	classId	name	grade
f83482e9252911eeb0140050569ff7ae	1	zhangsan	92
1eb977d7252a11eeb0140050569ff7ae	1	lisi	76
21cec849252a11eeb0140050569ff7ae	1	wangwu	33
2818e87a252a11eeb0140050569ff7ae	1	aa	12
2f4b8678252a11eeb0140050569ff7ae	2	bb	100
358e1b5b252a11eeb0140050569ff7ae	2	cc	55
4c909491252a11eeb0140050569ff7ae	2	dd	82
5314ee09252a11eeb0140050569ff7ae	2	ee	32
58fbb621252a11eeb0140050569ff7ae	2	ff	100

select id,classId,name,grade,rank() over(PARTITION by classId ORDER BY grade desc) as rk

from bcb;

查询结果：

id	classId	name	grade	rk
f83482e9252911eeb0140050569ff7ae	1	zhangsan	92	1
1eb977d7252a11eeb0140050569ff7ae	1	lisi	76	2
21cec849252a11eeb0140050569ff7ae	1	wangwu	33	3
2818e87a252a11eeb0140050569ff7ae	1	aa	12	4
2f4b8678252a11eeb0140050569ff7ae	2	bb	100	1
58fbb621252a11eeb0140050569ff7ae	2	ff	100	1
4c909491252a11eeb0140050569ff7ae	2	dd	82	3
358e1b5b252a11eeb0140050569ff7ae	2	cc	55	4
5314ee09252a11eeb0140050569ff7ae	2	ee	32	5

 

---

 

• 分组+字符串拼接

 group_concat()

companyId	contact	cellPhone
1	zhangsan	1678252888
1	lisi	1342224453
2	wangwu	167724322
2	xiaoli	19223535

 

select companyId,group_concat(cellPhone) from ccc group by companyId;

 

companyId	GROUP_CONCAT(cellPhone)
1	1678252888,1342224453
2	167724322,19223535

 

拼接的字符串中如果需要去重：select companyId,group_concat(distinct cellPhone) from ccc group by companyId;

---

 

• 比较判断 <> 会过滤 Null的数据

id	name
1	zhangsan
2	NULL
3	lisi
4	wangwu

 

select  * from bbb where name  <> 'zhangsan';

id	name
3	lisi
4	wangwu

 

如果需要查询出Null的数据，需要补充 select  * from bbb where name  <> 'zhangsan' or name is null;

注意mysql 区分Null 和空串

• find_in_set : 用于在一个逗号分隔的字符串列表中查询指定的值，并返回其在列表中的位置  

select find_in_set('a','b,c,d,a')  ： 返回结果 4