整体顺序部分倒序
创建表语句
[sql]
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
插入测试数据
[sql]
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：
[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做取负数处理，这样排序下来就自然是倒序
