mysql按年,月,日分组(group by)统计,count函数内添加筛选条件及其其他函数,使用方法

 

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},'%')
        OR
            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;

能够实现行列互换,当然,也可以配合其他函数或者条件使用.

 

待添加......