SQL - 按时间排序，然后按开头排列文本

Question

我在下面有一个查询：

SELECT DISTINCT TimeSched from tbl_schedule

它返回以下结果：

TimeSched
Rest Day
11:00 AM - 08:00 PM
No Schedule
09:00 AM - 06:00 PM
10:00 AM - 07:00 PM

当我使用 ORDER BY TimeSched 时，它会返回以下结果：

TimeSched
09:00 AM - 06:00 PM
10:00 AM - 07:00 PM
11:00 AM - 08:00 PM
No Schedule
Rest Day

但是，我想要的结果是我希望默认情况下在第一行和第二行上显示"休息日"和"无时间表"，然后按升序排列时间表的顺序。如下所示：

TimeSched
Rest Day
No Schedule
09:00 AM - 06:00 PM
10:00 AM - 07:00 PM
11:00 AM - 08:00 PM

Answer 1

试试这个。

select * from timetable
order by 
iif(timesched in ('No Schedule','Rest Day'),'01' + timesched,timesched)

为"无时间表"和"休息日"附加前缀 01 订购时..所以 01No 会去 1,01R 会去下一个，然后你的 09 到 24

Answer 2

如何简单地做：

SELECT DISTINCT TimeSched 
FROM tbl_schedule
ORDER BY (CASE WHEN timesched LIKE '[a-zA-Z]%' THEN 1 ELSE 0 END), 
         timesched;

Answer 3

尝试使用以下代码：

    DECLARE @TimeSched TABLE
    (ID INT IDENTITY(1,1),TimeSched VARCHAR(20))
    INSERT INTO @TimeSched
    VALUES
    ('Rest Day'),
    ('11:00 AM - 08:00 PM'),
    ('No Schedule'),
    ('09:00 AM - 06:00 PM'),
    ('10:00 AM - 07:00 PM')
    SELECT * FROM @TimeSched
    ORDER BY 
    CASE 
        WHEN TimeSched ='Rest Day' THEN 1
        WHEN TimeSched ='No Schedule' THEN 2
        ELSE 3 END

Answer 4

这是我问题的答案。

SELECT CASE TimeSched WHEN 'Restday' THEN 1 WHEN 'No Schedule' THEN 2 ELSE 3 END 
AS TimeSked, TimeSched ORDER BY TimeSked, TimeSched