多列排序/编号

Question

表格如下:
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

Answer 1

这列从何而来
seq
1
2
2
4

Answer 2

select *
from 
t
order by seq asc ,num1 asc,num2 asc ,date asc,name desc

Answer 3

select * from table 
order by num1,num2 desc,data,name desc

Answer 4

select * from tbname order by num1,num2 desc,data,name desc

Answer 5

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 @t
select * 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)
*/

Answer 6

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 @t
select * 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)
*/

Answer 7

一条语句应该解决不了问题!DECLARE @t TABLE (  
 ID VARCHAR(20),
 num1 INT,
 num2 DECIMAL(10,2),
 date VARCHAR(20),
 Name VARCHAR(20)
)INSERT INTO @t
 SELECT '01',2,1.3,'9-1','as' UNION ALL
 SELECT '02',1,-1,'9-2','ss' UNION ALL
 SELECT '03',1,-1,'9-2','ss' UNION ALL
 SELECT '04',1,-0.5,'9-2','ss' 
SELECT TOP 100 seq = IDENTITY(INT,1,1),* INTO # FROM @t ORDER BY NUM1,NUM2 DESC,DATE, NAME DESC, IDSELECT seq = (SELECT MIN(seq) FROM # A WHERE A.num1 = B.num1 AND A.num2 = B.num2 AND A.date = B.date),num1,num2,date,name,id
FROM # B
DROP TABLE #seq         num1        num2         date                 name                 id                   
----------- ----------- ------------ -------------------- -------------------- -------------------- 
1           1           -.50         9-2                  ss                   04
2           1           -1.00        9-2                  ss                   02
2           1           -1.00        9-2                  ss                   03
4           2           1.30         9-1                  as                   01（所影响的行数为 4 行）