skip to Main Content
SELECT
co.id,
IFNULL( oe.follow_status, 'unFollow' ) AS followStatus,
co.due_time 
FROM
car_order co
LEFT JOIN order_expire oe ON oe.order_id = co.id 
WHERE
co.type = '贷款' 
AND co.STATUS = '已放款' 
AND co.approve_status != '结清成功' 
AND date_FORMAT( co.due_time, '%Y-%m-%d' ) <= '2022-10-29' 
ORDER BY
field( oe.follow_status, 'unFollow', 'follow' ),
co.due_time DESC,
co.id DESC

enter image description here

The date sort is wrong. How should I write it?

2

Answers


  1. Chosen as BEST ANSWER
    SELECT
    co.id,
    IFNULL( oe.follow_status, 'unFollow' ) AS followStatus,
    co.due_time 
    FROM
    car_order co
    LEFT JOIN order_expire oe ON oe.order_id = co.id 
    WHERE
    co.type = '贷款' 
    AND co.STATUS = '已放款' 
    AND co.approve_status != '结清成功' 
    AND co.due_time <= '2022-10-29' 
    ORDER BY
    field( followStatus, 'unFollow', 'follow' ),
    co.due_time DESC,
    co.id DESC
    

    You should order by the IFNULL-alias followStatus instead.


  2. Assuming due_time be a timestamp column, you should be able to directly compare it against a date literal:

    SELECT co.id,
           IFNULL(oe.follow_status, 'unFollow') AS followStatus,
           co.due_time 
    FROM car_order co
    LEFT JOIN order_expire oe ON oe.order_id = co.id 
    WHERE co.type = '贷款' AND 
          co.STATUS = '已放款' AND
          co.approve_status != '结清成功' AND
          co.due_time <= '2022-10-29' 
    ORDER BY FIELD(oe.follow_status, 'unFollow', 'follow'),
             co.due_time DESC,
             co.id DESC;
    

    The DATE_FORMAT() function is used for converting dates/timestamps into strings, but you don’t need this here as date/timestamp columns can be compared directly.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search