我如何从表中获取周计划 [英] How I Can Take Week Plan From Table

问题描述

我从表中选择了我的记录，我希望按工作日订购，例如

在第一张唱片中

星期六

星期日

...

..



我的代码如下：

周是tb1中的字段。



从tb1中选择*其中Scode ='123'按周排序

i selected my records from table i want to order it by week days for example
in first record
saturday
sunday
...
..

my code is in below:
week is field in tb1.

select *from tb1 where Scode='123' order by week

推荐答案

您可以使用ID和DayName创建名为Weekdays的参考表，并在查询中使用它，或者我刚发现您可以创建伪表：



http://sqlmag.com/t-sql/alternatives-arrays [ ^ ]

Either you can create a reference table called Weekdays with ID and DayName and use it in your query or as I just discovered you can make pseudo tables:

http://sqlmag.com/t-sql/alternatives-arrays[^]

看到周列的类型为nvarchar（50） ，查询可以使用具体情况制定：

Seeing the week column is of type nvarchar(50), the query can be formulated using order by case :

--setup test data
declare @tab table(id int identity(1,1), week nvarchar(50));
insert into @tab
select N'sunday' week
union all select N'saturday' week
union all select N'monday' week
union all select N'friday' week
union all select N'wednesday' week
union all select N'tuesday' week
union all select N'wednesday' week
union all select N'saturday' week
union all select N'thursday' week
union all select N'sunday' week
;

--query order by case
select * 
from @tab
order by 
  case lower(week)
    when 'saturday' then 0
    when 'sunday' then 1
    when 'monday' then 2
    when 'tuesday' then 3
    when 'wednesday' then 4
    when 'thursday' then 5
    when 'friday' then 6
  end
;
--modify the case statements to get the desired order of the week

如果可能，请参考Tomas Takac关于按日期排序的建议（上面的评论）。

Take Tomas Takac suggestion (comment above) about order by date if possible.

这篇关于我如何从表中获取周计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！