1、mysql按月，按日分组统计数据
按月统计数据
```
select DATE_FORMAT(createtime,'%Y-%m'),count(*) from test where user =8 group by DATE_FORMAT(createtime,'%Y-%m');
```
按天统计数据
```
select DATE_FORMAT(createtime,'%Y-%m-%d'),count(*) from test where user =8 group by DATE_FORMAT(createtime,'%Y-%m-%d');
```
按季度统计数据-group by quarter
```
select DATE_FORMAT(createtime,'%Y-%m-%d'),count(*) from test where user =8 group by quarter(createtime);
```
2、mysql输出值 如果为null,输出指定值
```
IFNULL(z1.sum1,0)+IFNULL(z2.sum1,0) from
```
3、先查询所有的结果集，然后对结果集进行筛选聚合
```
select count(is_set = 0 or null) as noPrice from **；
```
4、查询插入批量数据（适合在离线情况下使用，否则会导致全表锁死）
```
insert into b(id,c_id,create_time,endtime) select * from a
```
5、IF判断查询输出
如果当前商品没有规格 则当前输出商品的价格，否则输出当前规格的价格
```
select
IF(
goods_standard_code is null, current_price, standard_price
) as price
from
a left join b on a.goods_id = b.goods_code
```
6、判断某个字段值是否包含于某个字符串中
语法：字段str2 的值 是否包含在 str1中，
注意：此语法 可以理解为 模糊匹配，举例说明：判断 “12” 字符串是否 “120,2,30,45”中，会返回true,因为模糊匹配到12 在120 中包含，可以使用以下方式进行解决
将字符串 “120,2,30,45”变为 “,120,2,30,45,” 也就是前后 各拼接一个逗号
将字符串“12” 变为 “,12,” 这样可以精准的进行匹配
```
INSTR(str1,str2);
```
7、列转行
场景举例：
记录今日的水果价格， 水果品种每天会有变化，以 fruit_type,fruit_price,create_date 进行记录，现在要求按天提取水果价格 成一行数据。水果类型正常情况下应该是一张字典表记录。这里为了简单讲清思路，直接转换
思路：
A、先将列数据根据行的要求进行 重命名
```
SELECT
DATE_FORMAT( create_date, "%Y-%m-%d" ) as day,
CASE fruit_type WHEN 1 THEN price end as "apple_price",
CASE fruit_type WHEN 2 THEN price end as "orange_price",
CASE fruit_type WHEN 3 THEN price end as "banana_price"
FROM
fruit_price
```
得出如下结果
B、再进行 转换为行
```
select
a.day,
GROUP_CONCAT(a.`apple_price`) as 'apple_price',
GROUP_CONCAT(a.`orange_price`) as 'orange_price',
GROUP_CONCAT(a.`banana_price`) as 'banana_price'
from
(SELECT
DATE_FORMAT( create_date, "%Y-%m-%d" ) as day,
CASE fruit_type WHEN 1 THEN price end as "apple_price",
CASE fruit_type WHEN 2 THEN price end as "orange_price",
CASE fruit_type WHEN 3 THEN price end as "banana_price"
FROM fruit_price) as a
GROUP BY a.day
```
结果：
8、日期处理
需求：查询按计划时间到了之后，没有处理的数据。如某一任务 要求延时7/3/1天，延时任务有bug，需要查询 未能正常处理的数据
语法：
date_add(要处理的时间,INTERVAL 间隔天数 DAY) 此时间就是应该解冻的时间
如
```
where 1=1
AND date_add( create_date, INTERVAL 7 DAY) <![CDATA[<]]> NOW()
```
9、查询group by中最后一条数据 和第一条数据 (group by 与 order by联合使用)
使用场景：查询用户某月期初积分数量，本期积分收入，本期积分支出，月末积分数量
sql示例：
```
select
'积分账户',
substring_index(group_concat(start_num order by id asc),',',1) as monthStartSum,
substring_index(group_concat(end_num order by id desc),',',1) as monthEndSum
from
jifen_update_detail where create_date between #{start} and #{end} DATE_FORMAT(create_date,'%Y-%m')
```
10、判断空串与null后输出
举例：用户的真实姓名为null的话，输出用户昵称
语法：if(LENGTH(trim(mma_xtmer_name))>0,real_name,user_name)
-----
持续更新中
网友评论