34,593
社区成员
发帖
与我相关
我的任务
分享
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE,
备注 VARCHAR(100)
)
GO
INSERT TBL
SELECT '2012-03-02','B' UNION ALL
SELECT '2012-03-05','C' UNION ALL
SELECT '2012-03-06','D' UNION ALL
SELECT '2012-03-07','E' UNION ALL
SELECT '2012-03-09','F' UNION ALL
SELECT '2012-03-11','G' UNION ALL
SELECT '2012-03-12','H' UNION ALL
SELECT '2012-03-13','I' UNION ALL
SELECT '2012-03-15','J' UNION ALL
SELECT '2012-03-19','K' UNION ALL
SELECT '2012-03-20','L'
go
create table #tbl(
日期 DATE,
备注 VARCHAR(100),
num int
)
go
insert #tbl
select *,ROW_NUMBER()over(order by 日期 asc) as num from tbl where 日期>=GETDATE()
union all
select *,ROW_NUMBER()over(order by 日期 desc) as num from tbl where 日期<GETDATE()
select 日期,备注 from #tbl
/*
日期 备注
2012-03-11 G
2012-03-12 H
2012-03-13 I
2012-03-15 J
2012-03-19 K
2012-03-20 L
2012-03-09 F
2012-03-07 E
2012-03-06 D
2012-03-05 C
2012-03-02 B
*/
-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE,
备注 VARCHAR(100)
)
GO
INSERT TBL
SELECT '2012-03-02','B' UNION ALL
SELECT '2012-03-05','C' UNION ALL
SELECT '2012-03-06','D' UNION ALL
SELECT '2012-03-07','E' UNION ALL
SELECT '2012-03-09','F' UNION ALL
SELECT '2012-03-11','G' UNION ALL
SELECT '2012-03-12','H' UNION ALL
SELECT '2012-03-13','I' UNION ALL
SELECT '2012-03-15','J' UNION ALL
SELECT '2012-03-19','K' UNION ALL
SELECT '2012-03-20','L'
--不使用union all
;with t
as(
select *,case when 日期>=getdate() then 1 else 0 end as A
from tbl
)
select 日期,备注 from t
order by a,(case when a=1 then 日期 end),
(case when a=0 then 日期 end) desc
/*
日期 备注
2012-03-09 F
2012-03-07 E
2012-03-06 D
2012-03-05 C
2012-03-02 B
2012-03-11 G
2012-03-12 H
2012-03-13 I
2012-03-15 J
2012-03-19 K
2012-03-20 L
*/