最近做一个项目,要求结果集按照城市和时间正序排列,但是某个城市按照时间倒序排列。
创建表语句
USE [master]
GO
/****** Object: Table [dbo].[Table_4] Script Date: 08/09/2013 14:30:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_4](
[time] [datetime] NULL,
[City] [varchar](10) NULL,
[count] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
插入测试数据
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '北京市' , 14 );
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '天津市' , 92 );
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '上海市' , 8 );
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '大连市' , 3 );
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '无锡市' , 142 );
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '深圳市' , 4 );
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-01 00:00:00.000' , '南京市' , 1 );
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-02 00:00:00.000' , '北京市' , 11 );
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-02 00:00:00.000' , '天津市' , 79 );
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-02 00:00:00.000' , '上海市' , 3 );
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-02 00:00:00.000' , '无锡市' , 92 );
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-02 00:00:00.000' , '深圳市' , 4 );
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-03 00:00:00.000' , '北京市' , 10 );
insert into [master].[dbo].[Table_4] ([time],[City],[count]) values( '2013-07-03 00:00:00.000' , '天津市' , 80 );
原表的查询结果
期望的结果是整体按照城市和时间顺序排序,然后在此基础上,天津市要按时间倒序排序,下面给出期望的结果
可以看出,整体是按城市拼音和时间顺序排序,但是在天津这个城市这里是倒序排序的,实现SQL:
select * from
(
SELECT ROW_NUMBER() over(partition by City order by time) as row_id,*
FROM [master].[dbo].[Table_4] t
) a order by City ,(case City when '天津市' then -1*row_id else row_id end)
这里用了一个小技巧,就是对于天津的row_id做取负数处理,这样排序下来就自然是倒序