T-SQL 记录排名问题.

[glc20022003]

目前记录集为
temp station year
23.1 51234 2003
22.1 51234 2004
20.1 51234 2006
18.1 51234 2005
20.1 51235 2006
23.1 51235 2003
22.1 51235 2004
18.1 51235 2005
18.1 51236 2005
23.1 51236 2003
22.1 51236 2004
20.1 51236 2006


要求用T-SQL根据每个station的temp由高到低得到排名如下
temp station year sort
23.1 51234 2003 1
22.1 51234 2004 2
20.1 51234 2006 3
18.1 51234 2005 4
20.1 51235 2006 3
23.1 51235 2003 1
22.1 51235 2004 2
18.1 51235 2005 4
18.1 51236 2005 4
23.1 51236 2003 1
22.1 51236 2004 2
20.1 51236 2006 3

[中国风]

select *,
(select count(1) from T where Station=a.Station and Temp!>a.Temp) as sort
from
T a

[中国风]

05:
select
*,
row_number()over(partition by Station order by Temp desc) as sort
from
T

[glc20022003]

row_number()over(partition by Station order by Temp desc) as sort
报没row_number这个函数

[glc20022003]

不对,我是要根据station分组来排序

[pt1314917]

select *,sort=(select count(1) from 表名 where station=a.station and temp>a.temp)+1
from 表名 a

[liangCK]

--示例数据
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50
GO

--1. 名次生成方式1,Score重复时合并名次
SELECT *,Place=(SELECT COUNT(DISTINCT Score) FROM tb WHERE Score>=a.Score)
FROM tb a
ORDER BY Place
/*--结果
Name       Score        Place 
---------------- ----------------- ----------- 
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        3
ff         76.00        4
bb         56.00        5
cc         56.00        5
ff         50.00        6
--*/



--2. 名次生成方式2,Score重复时保留名次空缺
SELECT *,Place=(SELECT COUNT(Score) FROM tb WHERE Score>a.Score)+1
FROM tb a
ORDER BY Place
/*--结果
Name       Score        Place 
--------------- ----------------- ----------- 
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        4
ff         76.00        5
bb         56.00        6
cc         56.00        6
ff         50.00        8
--*/

[glc20022003]

能排的出来,有没有效率高的,这样排的话效率太低了.

[liangCK]

2000好像只能这样
select *, 
(select count(1) from T where Station=a.Station and Temp!> a.Temp) as sort 
from   
T a

2005这样
select   
*, 
row_number()over(partition by Station order by Temp desc) as sort 
from   
T

[glc20022003]

SELECT *
,
          (SELECT COUNT(*)
         FROM (SELECT *
                 FROM (SELECT cast(AVG(cast(t1.maxtemp AS decimal(10, 2))) 
                               AS decimal(10, 2)) AS 日最高气温, t1.stationnum AS 区站号, 
                               2006 AS 年
                         FROM (SELECT MAX(maxtemp) AS maxtemp, 
                                       substring(ObservTimes, 1, 8) AS ObservTimes, 
                                       stationnum
                                 FROM tabTimeData
                                 WHERE (ObservTimes >= @st) AND 
                                       (ObservTimes <= @et) AND 
                                       (maxtemp NOT LIKE '%/%')
                                 GROUP BY substring(ObservTimes, 1, 8), stationnum) 
                               t1
                         GROUP BY t1.stationnum) t2
                 UNION
                 SELECT AVG(日最高气温) AS 日最高气温, 区站号, 年
                 FROM [day]
                 WHERE 月 >= @smonth AND 月 <= @emonth AND 日 >= @sday AND 日 <= @eday
                 GROUP BY 区站号, 年) B
         WHERE a.区站号 = b.区站号 AND B.日最高气温 > A.日最高气温) 
      + 1 AS 历史排名
FROM (SELECT *
        FROM (SELECT cast(AVG(cast(t1.maxtemp AS decimal(10, 2))) AS decimal(10, 2)) 
                      AS 日最高气温, t1.stationnum AS 区站号, 2006 AS 年
                FROM (SELECT MAX(maxtemp) AS maxtemp, substring(ObservTimes, 1, 8) 
                              AS ObservTimes, stationnum
                        FROM tabTimeData
                        WHERE (ObservTimes >= @st) AND 
                              (ObservTimes <= @et) AND 
                              (maxtemp NOT LIKE '%/%')
                        GROUP BY substring(ObservTimes, 1, 8), stationnum) t1
                GROUP BY t1.stationnum) t2
        UNION
        SELECT AVG(日最高气温) AS 日最高气温, 区站号, 年
        FROM [day]
        WHERE 月 >= @smonth AND 月 <= @emonth AND 日 >= @sday AND 日 <= @eday
        GROUP BY 区站号, 年) A
WHERE (年 = @eyear)
ORDER BY 区站号, 年

运行要25秒,谁帮忙看看,有没有什么办法优化.

[dawugui]

select * , sort = (select count(1) from tb where station = t.station and temp > t.temp) + 1 from tb t

[liangCK]

厉害.

[-狙击手-]

太长

[glc20022003]

不加排名次的话3秒就出来,加排名的话就要25秒,晕死了.

[glc20022003]

明天来结贴.祝大家元宵快乐.