1.date_sub() 时间的加减
备注:record 为datetime类型
select record_time as date, order_area as orderArea, order_cnt as orderCnt from dm_rpt_dss_enterprise_sum where seller_enterprise_id = #{enterpriseId} and record_time between date_sub(#{date},interval 15 day) and #{date} group by record_time order by record_time
2.date_format() 时间日期格式化
备注:record 为datetime类型
select DATE_FORMAT(record_time, '%Y-%m-%d') recordTime, permeability , orderincrement_30d as orderincrement30d, active_rate as activeRate, noorder_increment_90d as noorderIncrement90d, noorder_increment_30_90d as noorderIncrement30_90d, coverage_rate as coverageRate, ptial_cus_excavablearea as ptialCusExcavablearea, clue_cus_excavablearea as clueCusExcavablearea from dm_rpt_dss_enterprise_sum
3. now()获取当前时间
4.IFNULL() 处理值为null的情况
SELECT count(distinct customer_id) as customerNum, IFNULL( SUM(influence_quantity),0.00) as influenceQuantity FROM
customer_early_warning WHERE
to_days(record_time) = to_days(now()) AND seller_enterprise_id = #{sellerEnterpriseId}
5.convert() 转换函数
IFNULL( CONVERT ( d.orderArea / 10000, DECIMAL ( 18, 2 ) ), 0.00 ) AS orderArea
6.concat() 字符串拼接函数
CONCAT( province, '^', city, '^', area ) AS area
7.substr() 字符串截取
SUBSTR( a.record_time, 1, 10 ) >= concat( substr( #{queryDTO.queryDate}, 1, 7 ), '-01' )