-- 根据面试管理timeDate日期降序、timeDate时间升序,创建时间升序
SELECT id,create_time, (CASE WHEN JSON_VALID(interview.interviewer_json)
THEN
JSON_UNQUOTE(JSON_EXTRACT(interview.interviewer_json, CONCAT('$.','timeDate')))
ELSE
null
END) as timeDate
FROM td_neo_crm_customer_interview interview
-- WHERE CASE WHEN JSON_VALID(interview.interviewer_json)
-- THEN
-- JSON_UNQUOTE(JSON_EXTRACT(interview.interviewer_json, CONCAT('$.','timeDate'))) BETWEEN "2022-01-01 00:00" AND "2022-02-01 23:59:59"
-- ELSE
-- null
-- END
ORDER BY substring(timeDate,1,10) DESC, substring(timeDate,12,5) asc, create_time asc;