27,579
社区成员
发帖
与我相关
我的任务
分享
if object_id('tempdb..#a') is not null drop table #a
go
CREATE TABLE #a(name varchar(5));
insert into #a
select 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E'
--今天21号 ,datepart(dd,getdate())%5 取得值正好是1,
-- datepart(dd,getdate())%5+1就是显示你明天要的结果,
--或者把getdate() 改成'2014-2-22'测试明天的
;with cte as(
select *,row_number() over (order by getdate()) as m ,
datepart(dd,getdate())%5 as short from #a
)
select name from cte
order by case when short=m then 0 when m>short then 1 else 2 end ,m
f object_id('tempdb..#a') is not null drop table #a
go
CREATE TABLE #a(name varchar(5));
insert into #a
select 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E'
--今天21号 ,datepart(dd,getdate())%5 取得值正好是1,
-- datepart(dd,getdate())%5+1就是显示你明天要的结果,
--或者把getdate() 改成'2014-2-22'测试明天的
;with cte as(
select *,row_number() over (order by getdate()) as m ,
datepart(dd,getdate())%5 as short from #a
)
select name from cte
order by case when short=m then 0 when m>short then 1 else 2 end ,m
/*
第一个日期以 2-18 为A开头开始 2-19 B开头 2-20C开头 以此类推 GETDATE()为需要查询日期
*/
create table test(rn int,name char)
insert into test values(1,'A')
insert into test values(2,'B')
insert into test values(3,'C')
insert into test values(4,'D')
insert into test values(5,'E')
DECLARE @第一个日期 datetime
set @第一个日期='2014-02-18'
while CONVERT(varchar(100),GETDATE(), 23)>@第一个日期
begin
update test set rn=rn-1
update test set rn=(select MAX(rn) from test)+1 where rn=0
set @第一个日期=DATEADD(DAY,1,@第一个日期)
end
select * from test order by rn
if object_id('tempdb..#a') is not null drop table #a
go
CREATE TABLE #a(name varchar(5));
insert into #a
select 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E'
--今天21号 ,datepart(dd,getdate())%5 取得值正好是1,
-- datepart(dd,getdate())%5+1就是显示你明天要的结果,
--或者把getdate() 改成'2014-2-22'测试明天的
;with cte as(
select *,row_number() over (order by getdate()) as m ,
datepart(dd,getdate())%5 as short
from #a
)
select name from cte
order by case when short=m then 0 when m>short then 1 else 2 end ,m
/*
name
-----
A
B
C
D
E
*/