create_time
为数据库一时间相关字段,需要根据这个字段将查询到的数据按照年(月/日等)分组:
时间格式:
SELECT DATE_FORMAT(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days;
SELECT DATE_FORMAT(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks;
SELECT DATE_FORMAT(create_time,'%Y%m') months,COUNT(id) COUNT FROM role GROUP BY months
时间戳格式
SELECT FROM_UNIXTIME(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days;
SELECT FROM_UNIXTIME(create_time,'%Y%u') weeks,COUNT(id) COUNT FROM role GROUP BY weeks;
SELECT FROM_UNIXTIME(create_time,'%Y%m') months,COUNT(id) COUNT FROM role GROUP BY months
其他一些按照时间查询方式:
当天内的数据
select * from 表 where date(时间字段)=date(now())
select * from 表 where to_days(时间字段) = to_days(now());
最近N天内的数据
select * from 表 WHERE to_days(now()) - to_days(时间字段) <= N
当然也可以使用 date_sub()函数，即
select * from 表 WHERE DATE_SUB(NOW(), INTERVAL N DAY) <= date(时间字段)
查询最近一周数据
select * from 表 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段);
同理,查询 N 天内数据,只需将 7 换为 N,查询一月内换为 MONTH,一年内换为 YEAR.
查询 01-01 日至 02-02 日内的数据
select * from 表 where DATE_FORMAT(时间字段,'%m-%d') >= '01-01' and DATE_FORMAT(birthday,'%m-%d') <= '02-02';
查询两个时间字段相隔多少年(月/日/时...)
语法：TIMESTAMPDIFF(interval，datetime1，datetime2)，比较的单位interval可以为以下数值:
FRAC_SECOND表示间隔是毫秒, SECOND秒, MINUTE分钟, HOUR小时, DAY天, WEEK星期, MONTH月,QUARTER季度,
YEAR年. 时间单位为天也可以使用上面的 to_days 函数
count()
统计数量时,要筛选统计结果,比如表中有字段 number,color, 必须 number = 1 且 color = 'red' 时才统计:
select count(number=1 and color = 'red' or NULL) from table;
or Null 不可省略, 当 number=1 and color = 'red' 条件无匹配时,查询结果是 false 不是 null,count在 值是NULL是 不统计数.
select count(if(number=1 and color = 'red' true,null)) from table;
也可配合其他函数使用
select count(length(name) > 6 or NULL) from table
like 模糊查询:
SELECT * FROM user
WHERE
id LIKE CONCAT('%',#{id},'%')
name LIKE CONCAT(#{keyword},'%')
sum() 求和函数和 ifnull(),
select IFNULL(sum(a),0) as one
from test
where id = "123465";
需要与 ifnull() 搭配使用,因为当示例字段 a 无值时,sum()结果为null,这样使用返回值做计算时,就会抛出异常.使用ifnull()为查询结果添加默认值,避免发生异常.
replace()
使用场景一，查询结果展示时使用
select id，name，age，num from user where name = ‘张三’；
+----+----+----+----+
| id | name | age| num|
+----+----+----+----+
| 1 | 张三丰 | 18 | 111A |
1 rows in set (0.00 sec)
将查询结果的name字段的 ‘三’ 替换为 ‘四’ 展示
select id，replace（name，‘三’，‘四’）as newname，age，num from user where name = ‘张三’；
查询后结果 张三丰 就变成了 张四丰 了。
使用场景二，更新语句
update use set num = replace(num,'A','AA')；
使用场景三，插入或替换 语句 replace into
一般的插入语句数据,id 为生成的 uuid，直接使用 uuid() 函数,生成的是一个 36位长度的，例如:184fea35-341f-11e9-8ede-487b6bd31bf7，其中有4个字符是”-“ 的id。
insert into user(id,name,age，num) values(replace(uuid(), '-', ''),'李四','28'，'92A');
若想要每个用户只保存最近的一条数据，不重复录入且效率高，就可以使用 replace into 语句
replace into user(id,name,age，num) values(replace(uuid(), '-', ''),'李四','28'，'92A');
需要注意的是 replace into 是根据 唯一索引 或者 主键 来判断唯一的，也就是说可以把字段 name 设置为索引。
concat():
可以将查询结果进行拼接,使用方法为 concat(str1,str2,...) 如
select a,b from table
+----+----+
| a | b|
+----+----+
| aaa | bbb |
想要将两个字段拼接就可以使用这个函数,
select concat(a,'---',b) newStr from table
+----+
| newStr |
+----+
| aaa---bbb |
case when [expr] then [result1]...else [default] end
select years as 年份,
sum(case when quarter = "1" then num else 0 end) as 一季度,
sum(case when quarter = "2" then num else 0 end) as 二季度,
sum(case when quarter = "3" then num else 0 end) as 三季度,
sum(case when quarter = "4" then num else 0 end) as 四季度
from testa
group by years;
能够实现行列互换,当然,也可以配合其他函数或者条件使用.
待添加......
create_time 为数据库一时间相关字段,需要根据这个字段将查询到的数据按照年(月/日等)分组:时间格式: SELECT DATE_FORMAT(create_time,'%Y%m%d') days,COUNT(id) COUNT FROM role GROUP BY days; SELECT DATE_FORMAT(create_time,...
一、group by分组函数大家很熟悉，就是按照某一列进行分组排序。但是很多时候分组排序的时候，我们需要按照日或者月或者年来分组当前的数据。但是数据表中时间的字段是精确到分钟的，这种要怎么处理呢？
1. 其实很简单，只要找到当前表中的日期列，并且其转换成需要排序的年月格式便可，并且取出对应的字符长度。
2. 如下，我需要将金额数据按照月度汇总，那么我需要做的就是把当前日期先转换成年月...
--convert(nvarchar(10),CreateDate,120) => 2013-12-10
--print convert(varchar(100), getdate(), 112) => 20200809
--print convert(nvarchar(20),getdate(),20)...
SELECT
FROM_UNIXTIME(create_time, '%Y-%m-%d') as day, create_time,
count( DISTINCT uid ) AS amount
`black_production_user`
WHERE
create_time >= 1588262400
AND create_time <= 16
group by+列名，代表根据此列进行分组
可在分组中使用函数sum、max、avg等
group by要么不使用，如果使用，其后应该为select的所有的列，除非某些列比较特殊
举例如下：
数据表tmp内容如下，以下语句根据此表进行查询：
1、不使用group by，查询结果显示所有数据
1）当出现函数（sum、max、avg），并需要根据某列来计算函数时，必须group by；
如select dept,sum(salary) from tmp GROUP BY dept
