一、mysql最后一条数据的部分数据查不出来

mysql修改数据后没保存
多加几个数据，多试几次就行了

二、mysql降序升序(两个排序条件)

select
     hr.id,
     hr.hr_id,
     hr.customer_id,
     c.name as customer_name,
     c.service_team_id as team_id,
     st.name as team_name,
     hr.item_id,
     si.name as item_name,
     hr.push_cycle,
     hr.record_time,
     hr.hr_type,
     hr.push_status,
     hr.push_time,
     hr.commodity_ids,
     hr.service_item_ids,
     hr.remark,
     hr.tenant_id,
     hr.create_time,
     hr.update_time
 from
     health_record hr
 left join
     customer c
     on
         hr.customer_id = c.id

 left join
     service_team st
     on
         c.service_team_id = st.id
 left join
     service_item si
     on
         hr.item_id = si.id
 where
     1 = 1
 <if test="serviceType != null and serviceType != ''">
     and
     hr.hr_type = #{serviceType}
 if>
 <if test="serviceTeam != null and serviceTeam != ''">
     and
     c.service_team_id = #{serviceTeam}
 if>
 <if test="pushStatus != null and pushStatus != ''">
     and
     hr.pushStatus = #{pushStatus}
 if>
 <if test="consumerName != null and consumerName != ''">
     and
     c.name like concat('%', #{consumerName}, '%')
 if>
 <if test="buildStart != null and buildStart != ''">
     and
     hr.create_time >= #{buildStart}
 if>
 <if test="buildEnd != null and buildEnd != ''">
     and
     #{buildEnd} >= hr.create_time
 if>
 <if test="pushStart != null and pushStart != ''">
     and
     hr.push_time >= #{pushStart}
 if>
 <if test="pushEnd != null and pushEnd != ''">
     and
     #{pushEnd} >= hr.push_time
 if>
 order by
     push_status asc,
     create_time desc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

三、cron生成器

Calendar cal = Calendar.getInstance();//当前时间
int month = cal.get(Calendar.MONTH) + 1;//当前月份
int year = cal.get(Calendar.YEAR);//当前年
int quarter=0;//当前季度
int stm = 1;//季度的起始月份
int etm = 3;//季度的结束月份
switch (month){
    case 1: 
	    stm = 10; 
	    etm=12; 
	    quarter=3; 
	    year=year-1; 
    break;//因为一月份进入方法是统计上一年第四季度的信息，所以处理年份信息
    case 10: 
	    stm = 7; 
	    etm=9; 
	    quarter=2; 
    break;//本年
    case 7: 
	    stm = 4; 
	    etm=6; 
	    quarter=1; 
    break;//本年
    case 4: 
	    stm = 1; 
	    etm=3; 
	    quarter=0;  
    break;//本年
}
/** 最终得到 
		year---年份
		quarter--季度
		stm --起始月份
		etm终止月份
        */
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

四、分组查询并统计

SELECT 
	customer_id,metric,COUNT(*) 
FROM  
	customer_body_metrics   
GROUP BY 
	metric,customer_id
1
2
3
4
5
6

五、mysql时间条件查询

#今天
SELECT * FROM tb_log WHERE TO_DAYS(create_date) = TO_DAYS(NOW())
SELECT * FROM tb_log WHERE DATE_FORMAT(create_date,'%Y-%m-%d')=DATE_FORMAT(NOW(),'%Y-%m-%d')

#近3分钟
SELECT * FROM tb_log WHERE create_date >= DATE_SUB(NOW(),INTERVAL 3 MINUTE)

#近7天
SELECT * FROM tb_log WHERE create_date >= DATE_SUB(NOW(),INTERVAL 7 DAY)

#近30天
SELECT * FROM tb_log WHERE create_date >= DATE_SUB(NOW(),INTERVAL 30 DAY)

#本周
SELECT * FROM tb_log WHERE YEARWEEK(create_date ,1) = YEARWEEK(NOW(),7)

#本月
SELECT * FROM tb_log WHERE DATE_FORMAT(create_date,'%Y-%m')=DATE_FORMAT(NOW(),'%Y-%m')

#本季度
SELECT * FROM tb_log WHERE QUARTER(create_date)=QUARTER(NOW())

#本年度
SELECT * FROM tb_log WHERE YEAR(create_date)=YEAR(NOW())

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

六、上一季度的数据

#上一季度的数据
SELECT 
	customer_id,metric,COUNT(*) 
FROM  
	customer_body_metrics   
where 
	QUARTER(create_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER))
GROUP BY 
	metric,customer_id
1
2
3
4
5
6
7
8
9

七、分表查询并分组

SELECT 
	customer_id,metric,count(*)
FROM (
	SELECT customer_id,metric,create_time FROM customer_body_metrics UNION
	SELECT customer_id,metric,create_time FROM customer_body_metrics_0 UNION
	SELECT customer_id,metric,create_time FROM customer_body_metrics_1 UNION
	SELECT customer_id,metric,create_time FROM customer_body_metrics_2 UNION
	SELECT customer_id,metric,create_time FROM customer_body_metrics_3 UNION
	SELECT customer_id,metric,create_time FROM customer_body_metrics_4
)cbm
where 
	QUARTER(cbm.create_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER))
GROUP BY 
	metric,customer_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14

八、分表查询并分组且筛选

having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选

SELECT 
	customer_id,metric,count(*)
FROM (
	SELECT customer_id,metric,create_time FROM customer_body_metrics UNION
	SELECT customer_id,metric,create_time FROM customer_body_metrics_0 UNION
	SELECT customer_id,metric,create_time FROM customer_body_metrics_1 UNION
	SELECT customer_id,metric,create_time FROM customer_body_metrics_2 UNION
	SELECT customer_id,metric,create_time FROM customer_body_metrics_3 UNION
	SELECT customer_id,metric,create_time FROM customer_body_metrics_4
)cbm
where 
	QUARTER(cbm.create_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER))
GROUP BY 
	metric,customer_id
HAVING 
	count(*) >= 15
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

九、mysql去重

• distinct 语句中 select 显示的字段只能是 distinct 指定的字段，其他字段是不可能出现的。如下所示：select distinct name, id from A
  实际上是根据 “name+id” 来去重，distinct 同时作用在了 name 和 id 上。请大家牢记一点：distinct 会对结果集去重，对全部选择字段进行去重，并不能针对其中部分字段进行去重。
• 多个字段去重问题：利用min或者max函数

十、业务sql

查询本季度的客户指标表中，体重/血压/血糖检测次数超过15次的数据

SELECT 
	distinct customer_id
FROM (
	SELECT * FROM customer_body_metrics UNION
	SELECT * FROM customer_body_metrics_0 UNION
	SELECT * FROM customer_body_metrics_1 UNION
	SELECT * FROM customer_body_metrics_2 UNION
	SELECT * FROM customer_body_metrics_3 UNION
	SELECT * FROM customer_body_metrics_4
)cbm
where 
	DATE_FORMAT(cbm.create_time,'%Y-%m')=DATE_FORMAT(DATE_SUB(now(),interval 1 MONTH),'%Y-%m')
	and (
		cbm.metric = "weight"
	or 
		cbm.metric = "bloodPressure"
	or 
		cbm.metric = "bloodGlucose"
		)
GROUP BY 
	metric,customer_id
HAVING 
	count(*) >= 15
	

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

十一、lambdaQuerry去重查询