有表A,字段ID,Name,Type,Date
如果选择了Type值,则记录中此值排最前面,相同Type值的记录再以Date排序
举例表数据:
ID,Name,Type,Date
1 N1 T1 2010
2 N2 T3 2009
3 N9 T2 2011
4 N8 T3 1999
5 N4 T7 2110
6 N6 T2 2009
想到达到的效果如下:
选择了T2,则T2值的排最前面
3 N9 T2 2011
6 N6 T2 2009
1 N1 T1 2010
2 N2 T3 2009
4 N8 T3 1999
5 N4 T7 2110
如果选择了Type值,则记录中此值排最前面,相同Type值的记录再以Date排序
举例表数据:
ID,Name,Type,Date
1 N1 T1 2010
2 N2 T3 2009
3 N9 T2 2011
4 N8 T3 1999
5 N4 T7 2110
6 N6 T2 2009
想到达到的效果如下:
选择了T2,则T2值的排最前面
3 N9 T2 2011
6 N6 T2 2009
1 N1 T1 2010
2 N2 T3 2009
4 N8 T3 1999
5 N4 T7 2110
Type,Date DESC;
set @type = 1
select * from tab
ORDER BY CASE WHEN Type = @type THEN 0 ELSE 1 END,
Type,Date DESC;
if object_id('tempdb.dbo.#t') is not null drop table #t
go
create table #t (ID int,Name varchar(2),Type varchar(2),Date int)
insert into #t
select 1,'N1','T1',2010 union all
select 2,'N2','T3',2009 union all
select 3,'N9','T2',2011 union all
select 4,'N8','T3',1999 union all
select 5,'N4','T7',2110 union all
select 6,'N6','T2',2009select * from #t
order by case when type='T2' then null else type endID Name Type Date
----------- ---- ---- -----------
3 N9 T2 2011
6 N6 T2 2009
1 N1 T1 2010
2 N2 T3 2009
4 N8 T3 1999
5 N4 T7 2110(所影响的行数为 6 行)
(
ID int identity(1,1) primary key,
Name nvarchar(20),
Type nvarchar(20),
Date int
)
insert into #TT select 'N1','T1','2010'
insert into #TT select 'N2','T3','2009'
insert into #TT select 'N9','T2','2011'
insert into #TT select 'N8','T3','1999'
insert into #TT select 'N4','T7','2110'
insert into #TT select 'N6','T2','2009'select * from #TT order by case when Type='T2' then 0 else 1 end,DateID Name Type Date
----------- -------------------- -------------------- -----------
6 N6 T2 2009
3 N9 T2 2011
4 N8 T3 1999
2 N2 T3 2009
1 N1 T1 2010
5 N4 T7 2110(6 行受影响)
(
ID int identity(1,1) primary key,
Name nvarchar(20),
Type nvarchar(20),
Date int
)
insert into #TT select 'N1','T1','2010'
insert into #TT select 'N2','T3','2009'
insert into #TT select 'N9','T2','2011'
insert into #TT select 'N8','T3','1999'
insert into #TT select 'N4','T7','2110'
insert into #TT select 'N6','T2','2009'select * from #TT order by case when Type='T2' then 0 else 1 end,Type,DateID Name Type Date
----------- -------------------- -------------------- -----------
6 N6 T2 2009
3 N9 T2 2011
1 N1 T1 2010
4 N8 T3 1999
2 N2 T3 2009
5 N4 T7 2110(6 行受影响)
解决了问题。