When we write business code , You will often encounter that the sorting method is neither positive nor reverse .
for example ： There is a simple task task surface , The table structure is as follows ：
id ： Mission ID
start-time： Mission start time
end_time： Mission end time
deal_status： Task status 0 Not yet begun 1 Have in hand 2 Disposed of
Sort by custom state ：
positive sequence ： Have in hand 、 Not yet begun 、 Disposed of
In reverse order ： Disposed of 、 Not yet begun 、 Have in hand
Find out whether it's asc still desc Can't achieve . At this time, we can use mysql Of field Realization .
-- positive sequence SELECT * FROM `task` order by field(deal_status,1,0,2) -- In reverse order SELECT * FROM `task` order by field(deal_status,2,0,1)
Add delay status , That is, the actual interface returns the status ：0 Not yet begun 1 Have in hand 2 Disposed of 3 delay （ current time >end_time And unprocessed tasks ）
Sort by the following states
positive sequence ： Have in hand 、 Not yet begun 、 delay 、 Disposed of
In reverse order ： Disposed of 、 delay 、 Not yet begun 、 Have in hand
At this point, we found that the use of demand 1 The way inside doesn't solve the problem , You can use mysql Inside case To reassign the original state , For personalized sorting
SELECT id,start_time,end_time,deal_status, CASE WHEN deal_status = 0 THEN 1 WHEN deal_status = 1 THEN 0 WHEN deal_status = 2 THEN 3 WHEN deal_status != 2 AND NOW() > end_time THEN 2 ELSE deal_status END sort_status FROM `task` order by sort_status asc/desc
The above two sorting methods can meet most of the requirements , But there is no upper limit to the product's brain hole , Requirements are as follows ：
Sort by the following states ： Have in hand delay Not yet begun Disposed of
Same state （ Except processed ） The order of presentation is ：
① The deadline is in reverse order
② Reverse the starting time
The display order of processed status is the reverse of processing completion time .
Although this demand can also be realized through demand 2 The way inside is to solve , But for the sake of more complex sorting, and for the sake of system performance , I'll just add sort Field （bigint）.
Ideas ： Every time you add or modify data, you will calculate the weight of this line of data , And then sort by weight . Delayed tasks are weighted daily by scheduled tasks
Calculation formula ：( Start timestamp + Deadline stamp * 2 )+ state [20 Have in hand 15 delay 10 Not yet begun 5 Disposed of ] * Current timestamp