Is it possible to select a specific ORDER BY in SQ

问题:

I have a table that holds days and times, the day column, can have any of the seven days entered into it, and they are set to data type varchar. As this table holds booking times for a client, I want to select all days from the table where the id matches, and I want to sort by day Monday-Sunday. I was hoping that I could add something to this query to manually select the order the results come back like so:

select * 
from requirements 
where Family_ID = 1 
ORDER BY Day, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

This of course doesn't work but I just wanted to show what I am trying to achieve. The client doesn't necessarily require help every day, I just want to show the days they are booked in.

Sorting by DESC and ASC doesn't help with days of the week, I would appreciate any tips on how to achieve this.

Thanks.

回答1:

Hmm.. that's nasty, the days are stored as verbatim 'Monday', 'Tuesday', etc?

Anyway, just do this:

SELECT * 
FROM Requirements
ORDER BY 
     CASE Day 
     WHEN 'Monday' THEN 1
     WHEN 'Tuesday' THEN 2
     WHEN 'Wednesday' THEN 3
     WHEN 'Thursday' THEN 4
     WHEN 'Friday' THEN 5
     WHEN 'Saturday' THEN 6
     WHEN 'Sunday' THEN 7
     END

回答2:

IMHO you don't have any reason to store Monday, Tuesday, etc ... If you store the date or datetime value, you can always extract the weekday name from that data at query time when you need it - either using DATENAME in your query or using the functionality in your presentation tier. The performance cost of doing so doesn't justify storing it separately IMHO. And you should still be able to sort correctly using the native date/datetime data.