查询时间段（效率最高）
SELECT 字段名 from 表名 WHERE 字段名 BETWEEN DATE_SUB(NOW(), INTERVAL 天数 DAY) AND NOW()
查询时间差
select datediff(NOW(),字段名)
字段拼接
concat_ws(分隔符，字段名1,字段名2) as 自定义字段名
拼接效果
自定义字段名
字段名1：字段名2
判断查询，两种方式，
表字段
1 替换查询
concat_ws(":",REPLACE(REPLACE(REPLACE(REPLACE(io.return_tool, '2', '高铁' ),'0','大巴'),'1','普快'),'3','飞机'),io.return_flight)
查询结果
2 判断查询
CASE case_value
WHEN when_value THEN
statement_list
ELSE
statement_list
END CASE;
(case te.period when 0 then te.temperature end) as temperature_mor
当period=0，te.temperature赋值给temperature_mor字段
多条结果合并或者合并再拼接到一条记录
1 .合并
SELECT
test.id,test.office_name,test.`name`,test.phone,test.home_address,test.current_address,test.back_time, test.days,
GROUP_CONCAT(test.temperature_mor) as temperature_mor,
GROUP_CONCAT(test.temperature_aft) as temperature_aft,
GROUP_CONCAT(test.is_exp_mor) as is_exp_mor,
GROUP_CONCAT(test.is_exp_aft) as is_exp_aft
from
(SELECT
ri.id,ri.office_name,ri.`name`,ri.phone,ri.home_address,ri.current_address,ri.back_time, datediff(NOW(), back_time) as days,
(case te.period when 0 then te.temperature end) as temperature_mor,
(case te.period when 1 then te.temperature end) as temperature_aft,
(case te.period when 0 then te.is_exp end) is_exp_mor,
(case te.period when 1 then te.is_exp end) is_exp_aft
from nstadmin_rinformation ri
LEFT JOIN nstadmin_temperature te on ri.phone=te.phone
WHERE ri.office_mode="1" ) test
GROUP BY test.`name`
2.合并并拼接
GROUP_CONCAT(CONCAT("上午：",test.temperature_mor,"下午：",test.temperature_aft)) as temperature,
GROUP_CONCAT(CONCAT("上午：",test.is_exp_mor,"下午：",test.is_exp_aft)) as is_exp,
查询当年某一天
#当年第一天：
SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY);
#当年最后一天：
SELECT concat(YEAR(now()),'-12-31');
