MySQL（二）高阶1. 常用函数 1.1. 时间函数年：YEAR() 1.2. 当前日期 1.2. 年龄：TIMES

1. 常用函数

1.1. 时间函数

年：YEAR()

YEAR(current_date)-YEAR(jysj)

1.2. 当前日期

current_date

1.2. 年龄：TIMESTAMPDIFF

TIMESTAMPDIFF(YEAR, csny, CURDATE()) 年龄

1.3. 获取json中的值：JSON_EXTRACT

JSON_EXTRACT(t.列名, '$[*].id') FROM 表名

1.4. 获取数组中的json值：JSON_EXTRACT

SELECT JSON_EXTRACT(列名, '$[0]. 要取的字段') FROM 表名
SELECT 列名->'$[0].要取的字段' FROM 表名

1.5. 这样获取的结果会有引号，通过JSON_UNQUOTE 函数去掉引号

JSON_UNQUOTE：去掉引号

SELECT syd,JSON_UNQUOTE(JSON_EXTRACT(syd, '$[0].name')) 生源地,syd->>'$[0].name' 生源地 FROM data_repository_4

2. 复杂查询

2.1. 多表联查

2.2. 嵌套查询

SELECT 年龄,总人数,人数/总人数 FROM ((SELECT TIMESTAMPDIFF(YEAR, csny, CURDATE()) 年龄,COUNT(*) 人数 FROM data_repository_4 a LEFT JOIN data_repository_20 b ON a.creator =b.creator WHERE xslb='本科生' GROUP BY 年龄 ORDER BY 年龄) t1 join (select count(*) 总人数 from data_repository_20 WHERE xslb='本科生') t2 on 1=1)

blog.csdn.net/cao47820824…

Mysql语句是：select count(*) from (select * from dede_spacemoney group by sid) ;

当执行的时候抛出了这个异常，原来在用进行嵌套查询的时候，子查询(select * from dede_spacemoney group by sid)执行出来的的结果是需要作为一个派生表来给上一级进行查询(select count(*) from)使用的，所以子查询的结果必须要有一个别名，这里“别名”的使用可以理解为是用作临时变量记录子查询的结果

把MySQL语句改成：select count(*) from (select * from list where name="xiao") as t;

问题解决！

使用group by子句如何显示count()等于0的结果

blog.csdn.net/qq_24309787…

逻辑(if)判断 CASE WHEN THEN

SELECT CASE  
WHEN TIMESTAMPDIFF(YEAR, csrq, CURDATE())<=29 THEN '29岁及以下'
WHEN (TIMESTAMPDIFF(YEAR, csrq, CURDATE())>=30 AND TIMESTAMPDIFF(YEAR, csrq, CURDATE())<=34) THEN '30到34岁'
WHEN (TIMESTAMPDIFF(YEAR, csrq, CURDATE())>=35 AND TIMESTAMPDIFF(YEAR, csrq, CURDATE())<=39) THEN '35到39岁'
WHEN (TIMESTAMPDIFF(YEAR, csrq, CURDATE())>=40 AND TIMESTAMPDIFF(YEAR, csrq, CURDATE())<=44) THEN '40到44岁'
WHEN (TIMESTAMPDIFF(YEAR, csrq, CURDATE())>=45 AND TIMESTAMPDIFF(YEAR, csrq, CURDATE())<=49) THEN '45到49岁'
WHEN (TIMESTAMPDIFF(YEAR, csrq, CURDATE())>=50 AND TIMESTAMPDIFF(YEAR, csrq, CURDATE())<=54) THEN '50到54岁'
WHEN (TIMESTAMPDIFF(YEAR, csrq, CURDATE())>=55 AND TIMESTAMPDIFF(YEAR, csrq, CURDATE())<=59) THEN '55到59岁'
WHEN (TIMESTAMPDIFF(YEAR, csrq, CURDATE())>=60 AND TIMESTAMPDIFF(YEAR, csrq, CURDATE())<=64) THEN '60到64岁'
ELSE '65岁及以上' END AS 年龄,COUNT(*) 人数 FROM data_repository_21 WHERE zgzt->>'$[0].name'='在职' AND status=0 GROUP BY 年龄 ORDER BY 年龄

去重

DISTINCT
分组

查询自定义排序

SELECT * FROM [dbo].[SalesPerformance]
ORDER BY CASE 
    WHEN [Salesman] = 'S0008' THEN '1'
    WHEN [Salesman] = 'S0032' THEN '2'
    WHEN [Salesman] = 'S0022' THEN '3'
    ELSE [Salesman] END ASC

MySQL可以通过field()函数自定义排序，格式：field(value,str1,str2,str3,str4)，value与str1、str2、str3、str4比较，返回1、2、3、4，如遇到null或者不在列表中的数据则返回0.

MySQL（二）高阶