多列排序/编号
表格如下:
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)*/
------解决方案--------------------
一条语句应该解决不了问题!
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, ID
SELECT 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 行）