system_order 中有 字段
APPLY_START_DATE 2018-03-06 00:00:00
START_DATE 2018-03-06
REFUSAL_REASON RF001
SELECT COUNT(*) FROM system_orderWHERE province = '澳門'
AND START_DATE >= '2018-03-06'
AND START_DATE <= '2018-03-06'
AND REFUSAL_REASON LIKE 'RF%'
結果為1
SELECT COUNT(*) FROM system_order
WHERE province = '澳門'
AND APPLY_START_DATE >= '2018-03-06'
AND APPLY_START_DATE <= '2018-03-06'
AND REFUSAL_REASON LIKE 'RF%'
結果為0
SELECT COUNT(*) FROM system_order
WHERE province = '澳門'
AND TO_DAYS(APPLY_START_DATE) >= TO_DAYS('2018-03-06')
AND TO_DAYS(APPLY_START_DATE) <= TO_DAYS('2018-03-06')
AND REFUSAL_REASON LIKE 'RF%'
結果為1
因為SELECT TO_DAYS('2018-03-06')
等於 737124
SELECT TO_DAYS('2018-03-06 00:00:00') 也等於 737124
mybatis 的 mapper.xml中可以這樣寫
select count(*) from system_order
and province = #{province}
and product_channel = #{product_channel}
and TO_DAYS(apply_start_date) = ]]> TO_DAYS(#{start_date})
and TO_DAYS(apply_start_date) TO_DAYS(#{end_date})