php - 我在数据库上有 date_from 和 date_to 列......我如何根据 date_from 和 date_to 显示所有记录作为范围日期

Question

SELECT la.emp_id      'EMPLOYEE ID'
     , ta.surname      SURNAME
     , ta.firstname    FIRSTNAME
     , ta.middlename   MIDDLENAME
     , la.department   DEPARTMENT
     , la.leave_type  'LEAVE TYPE'
     , la.date_from   'FROM DATE'
     , la.date_to     'TO DATE'
     , la.date_filed  'DATE FILED'
     , la.num_days    'DAYS ABSENT'
     , la.date_return 'DATE RETURN'
     , la.reason       REASON
     , ta.rem_vl      'REMAINING VL'
     , ta.rem_sl      'REMAINING SL'
     , la.remarks      REMARKS
  FROM leave_application la
  JOIN tsp_employee_account ta 
    ON la.emp_id = ta.emp_id
 WHERE la.date_from = '2017-04-22'
   AND la.date_to = '2017-04-22'
   AND la.filed != 0
 ORDER 
    BY la.leave_id DESC 
 LIMIT 0,30;

我想根据数据库中的 date_from 和 date_to 根据输入的起始日期和截止日期显示所有记录。我怎样才能做到这一点？

Answer 1

可以这样写

SELECT la.emp_id AS  'EMPLOYEE ID', ta.surname AS SURNAME, ta.firstname AS FIRSTNAME, ta.middlename AS MIDDLENAME, la.department AS  'DEPARTMENT', la.leave_type AS  'LEAVE TYPE', la.date_from AS  'FROM DATE', la.date_to AS  'TO DATE', la.date_filed AS  'DATE FILED', la.num_days AS  'DAYS ABSENT', la.date_return AS  'DATE RETURN', la.reason AS  'REASON', ta.rem_vl AS  'REMAINING VL', ta.rem_sl AS  'REMAINING SL', la.remarks AS  'REMARKS'
FROM leave_application la
JOIN tsp_employee_account ta ON la.emp_id = ta.emp_id
WHERE DATE(la.date_from) >= '2017-04-22' -- <<--- check this
AND DATE(la.date_to) <= '2017-04-22' -- <<--- check this
AND la.filed !=  '0'
ORDER BY la.leave_id DESC 
LIMIT 0 , 30;