以mysql为例进行排序

SELECT * 
FROM case_study
ORDER BY CASE 

WHEN expiry_date_case > CURDATE() THEN 3 

WHEN expiry_date_case IS NULL  THEN 2

WHEN expiry_date_case < CURDATE() THEN 1 

END DESC

The above query work's fine, But i want to sort the items by expiry date in ASC in one case and DESC in one case.How to acheive this it should be some thing like this

pseudo query

WHEN expiry_date_case > CURDATE() THEN 3 expiry_date_case ASC

WHEN expiry_date_case IS NULL  THEN 2

WHEN expiry_date_case < CURDATE() THEN 1 expiry_date_case DESC

Here is a more generic form of doing the sorting, where you can use multiple conditions for ordering other than date

SELECT *
FROM case_study
ORDER BY 
 CASE 
   WHEN expiry_date_case > CURDATE() THEN 3 
   WHEN expiry_date_case IS NULL  THEN 2
   WHEN expiry_date_case < CURDATE() THEN 1 
END DESC,
case when expiry_date_case > CURDATE() then expiry_date_case end,
case when expiry_date_case < CURDATE() then expiry_date_case end desc

Try this:

SELECT *
FROM case_study
ORDER BY CASE 
            WHEN expiry_date_case > CURDATE() THEN 3 
            WHEN expiry_date_case IS NULL  THEN 2
            WHEN expiry_date_case < CURDATE() THEN 1 
         END DESC,
         ABS(DATEDIFF(CURDATE(), expiry_date_case)) 

All records:

1. having expiry_date_case past CURDATE() will come first,
2. then come NULL records, followed by,
3. records having expiry_date_case < CURDATE().

Group [1] records will be in ascending order (within their own group), whereas group [3] records will be in descending order.

Demo here