SQL查询时间数据和工资评级问题笔记

查询最近几天，几月，几年的数据
select *
FROM 表名
where date(列名)>=DATE_SUB(CURDATE(), INTERVAL 3 DAY)

select *
FROM 表名
where date(列名)>=DATE_SUB(CURDATE(), INTERVAL 3 MONTH)

select *
FROM 表名
where date(列名)>=DATE_SUB(CURDATE(), INTERVAL 3 YEAR)

常见工资评级问题
SELECT
CASE WHEN salary <= 500 THEN ‘1’
WHEN salary > 500 AND salary <= 600 THEN ‘2’
WHEN salary > 600 AND salary <= 800 THEN ‘3’
WHEN salary > 800 AND salary <= 1000 THEN ‘4’
ELSE NULL END salary_class, – 别名命名
COUNT(*) FROM Table_A
GROUP BY
CASE WHEN salary <= 500 THEN ‘1’
WHEN salary > 500 AND salary <= 600 THEN ‘2’
WHEN salary > 600 AND salary <= 800 THEN ‘3’
WHEN salary > 800 AND salary <= 1000 THEN ‘4’
ELSE NULL END;
统计12个月份的数据sum(case when)

select
sum(case month(列名) when ‘1’ then 1 else 0 end) as 一月份,
sum(case month(列名) when ‘2’ then 1 else 0 end) as 二月份,
sum(case month(列名) when ‘3’ then 1 else 0 end) as 三月份,
sum(case month(列名) when ‘4’ then 1 else 0 end) as 四月份,
sum(case month(列名) when ‘5’ then 1 else 0 end) as 五月份,
sum(case month(列名) when ‘6’ then 1 else 0 end) as 六月份,
sum(case month(列名) when ‘7’ then 1 else 0 end) as 七月份,
sum(case month(列名) when ‘8’ then 1 else 0 end) as 八月份,
sum(case month(列名) when ‘9’ then 1 else 0 end) as 九月份,
sum(case month(列名) when ‘10’ then 1 else 0 end) as 十月份,
sum(case month(列名) when ‘11’ then 1 else 0 end) as 十一月份,
sum(case month(列名) when ‘12’ then 1 else 0 end) as 十二月份
from 表名
where year(列名)=‘2018’;

then 1 ==> 1为常量 相当于count（*）

可以替换为列名 :
sum(case month(列名) when ‘1’ then 列名 else 0 end) as 一月份,

分组拼接GroupConcat
select GROUP_CONCAT(CONCAT(列名1,列名2)) as 新列名
from 表名

结果如下：
a1,a2,a3

查询时间数据
1 数据库字段pk_time(Varchar)
当天的数据
SELECT * FROM 表 WHERE date(fk_time) = curdate();
当月的数据
SELECT *FROM 表 WHERE DATE_FORMAT(fk_time,’%Y%m’)=DATE_FORMAT(CURDATE( ),’%Y%m’)
昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, ‘%Y%m’ ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m’ )
上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m’ ) , date_format( 时间字段名, ‘%Y%m’ ) ) =1
查询本季度数据
select * from ht_invoice_information where QUARTER(create_date)=QUARTER(now());
查询上季度数据
select * from ht_invoice_information where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
查询本年数据
select * from ht_invoice_information where YEAR(create_date)=YEAR(NOW());
查询上年数据
select * from ht_invoice_information where year(create_date)=year(date_sub(now(),interval 1 year));
查询当前这周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now());
查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now())-1;
查询当前月份的数据
select name,submittime from enterprise where date_format(submittime,’%Y-%m’)=date_format(now(),’%Y-%m’)
查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
2 时间段数据
id 为方法名,parameterType参数用map保存,resultType为返回对象 参数tj_start tj_end 提交开始和结束时间

<select id="方法名" parameterType="参数类型" resultType="返回类型">
   select *from jw_order where 1=1            
   <if test="tj_start!= null and tj_start!=''">
    AND submittime&gt;=#{tj_start}
   </if>
   <if test="tj_end!=null and tj_end!=''">
    AND submittime&lt;=#{tj_end}
   </if>
</select>

3 模糊查询sql(Mybatis)

select *from 表名 where 1=1
AND 字段 LIKE CONCAT(CONCAT(’%’, #{参数}), ‘%’)