select * from tbl where 会议时间>getdate() order by 会议时间 asc union all select * from tbl where 会议时间<getdate() order by 会议时间 desc
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 */
谢谢 但能不能不要用 union all 方式 因为我要做分页而且我用的分页是一个通用的方法 有没有 Select * From MyMeeting Order By ???? 这种写法,直接写OrderBy的 谢谢
好像还可以用case when 实现,电脑环境关了,明天吧
select * from tb order by case when meetingtime>=getdate() then 0 else 1 end,abs(datediff(ss,getdate(),meetingtime)) asc
select * from (select top 100 percent * from tb where dt <= '会议时间' order by dt) m union all select * from (select top 100 percent * from tb where dt > '会议时间' order by dt desc) n
select *from(rselect *,case when date <getdate() then 0 else 1 end as a from t)b order by a,(case when a =1 then date end),(case when a=0 then date end)deas
select *from(rselect *,case when date <getdate() then 0 else 1 end as a from t)b order by a,(case when a =1 then date end),(case when a=0 then date end)deas
-->生成测试数据: 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 */
union all
select * from tbl where 会议时间<getdate() order by 会议时间 desc
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
*/
但能不能不要用
union all
方式
因为我要做分页而且我用的分页是一个通用的方法
有没有
Select * From MyMeeting Order By ????
这种写法,直接写OrderBy的
谢谢
select * from (select top 100 percent * from tb where dt <= '会议时间' order by dt) m
union all
select * from (select top 100 percent * from tb where dt > '会议时间' order by dt desc) n
-->生成测试数据:
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
*/