表格如下:
id num1 num2 date name
01 2 1.3 9-1 as
02 1 -1 9-2 ss
03 1 -1 9-2 ss
04 1 -0.5 9-2 ss
.. .. .. ... ..
num1是正整数,NUM2是实数(包括负数),
希望分别按照num1升序,num2降序,date升序,name降序的顺序编号,重复排名保留名次
seq num1 num2 date name id
1 1 -0.5 9-2 ss 04
2 1 -1 9-2 ss 02
2 1 -1 9-2 ss 03
4 2 1.3 9-1 as 01
------解决方案--------------------
select *
from
t
order by seq asc ,num1 asc,num2 asc ,date asc,name desc
------解决方案--------------------
select * from table
order by num1,num2 desc,data,name desc
------解决方案--------------------
select * from tbname order by num1,num2 desc,data,name desc
------解决方案--------------------
- SQL code
declare @t table(ID int,num1 int,num2 float,date char(10),name char(10))insert into @t select 01,2,1.3,'09-01','as'insert into @t select 02,1,-1,'09-02','ss'insert into @t select 03,1,-1,'09-01','as'insert into @t select 03,1,-1,'09-01','ss'insert into @t select 04,1,-0.5,'09-02','ss'select * from @tselect * from @t order by num1 asc,num2 desc,date asc ,name desc/*ID num1 num2 date name----------- ----------- ---------------------- ---------- ----------1 2 1.3 09-01 as 2 1 -1 09-02 ss 3 1 -1 09-01 as 3 1 -1 09-01 ss 4 1 -0.5 09-02 ss (5 row(s) affected)ID num1 num2 date name----------- ----------- ---------------------- ---------- ----------4 1 -0.5 09-02 ss 3 1 -1 09-01 ss 3 1 -1 09-01 as 2 1 -1 09-02 ss 1 2 1.3 09-01 as (5 row(s) affected)*/
------解决方案--------------------
- SQL code
declare @t table(ID int,num1 int,num2 float,date char(10),name char(10))insert into @t select 01,2,1.3,'09-01','as'insert into @t select 02,1,-1,'09-02','ss'insert into @t select 03,1,-1,'09-01','as'insert into @t select 03,1,-1,'09-01','ss'insert into @t select 04,1,-0.5,'09-02','ss'select * from @tselect * from @t order by num1 asc,num2 desc,date asc ,name desc/*ID num1 num2 date name----------- ----------- ---------------------- ---------- ----------1 2 1.3 09-01 as 2 1 -1 09-02 ss 3 1 -1 09-01 as 3 1 -1 09-01 ss 4 1 -0.5 09-02 ss (5 row(s) affected)ID num1 num2 date name----------- ----------- ---------------------- ---------- ----------4 1 -0.5 09-02 ss 3 1 -1 09-01 ss 3 1 -1 09-01 as 2 1 -1 09-02 ss 1 2 1.3 09-01 as (5 row(s) affected)*/
------解决方案--------------------