怎么求得每个班分数最后的2名？

Question

班级    分数
A       30
A       50
C       52
D       30
A       84
E       68
A       70
B       32如题怎么按班级分组后求出，分数最底的两名

Answer 1

if OBJECT_ID('tempdb..#temp', 'u') is not null   drop table #temp;
go
create table #temp( [班级] varchar(100), [分数] INT);
insert #temp
select 'A','30' union all
select 'A','50' union all
select 'C','52' union all
select 'D','30' union all
select 'A','84' union all
select 'E','68' union all
select 'A','70' union all
select 'B','32' --SQL:
;WITH CTE AS
(
SELECT ROWID=ROW_NUMBER() OVER(PARTITION BY [班级] ORDER BY 分数 DESC),*
from #temp
)
SELECT [班级],分数 FROM CTE
WHERE ROWID <= 2
/*
班级 分数
A 84
A 70
B 32
C 52
D 30
E 68
*/

Answer 2

--或者:
SELECT b.* FROM
(SELECT DISTINCT [班级] FROM #temp) a
CROSS APPLY
(SELECT TOP(2) * FROM #temp m WHERE m.[班级] = a.[班级] ORDER BY 分数 DESC) b
/*
班级 分数
A 84
A 70
B 32
C 52
D 30
E 68
*/

Answer 3

if OBJECT_ID('tempdb..#temp', 'u') is not null   drop table #temp;
go
create table #temp( [班级] varchar(100), [分数] INT);
insert #temp
select 'A','30' union all
select 'A','50' union all
select 'C','52' union all
select 'D','30' union all
select 'A','84' union all
select 'E','68' union all
select 'A','70' union all
select 'B','32' 
 
--SQL:
;WITH CTE AS
(
    SELECT ROWID=DENSE_RANK() OVER(PARTITION BY [班级] ORDER BY 分数 ASC),*
    from #temp
)
SELECT [班级],分数 FROM CTE
WHERE ROWID <= 2

Answer 4

cross join会，但这里是cross apply，不会。
你上网或MSDN搜一下apply的用法，就明白啦。

Answer 5

不会产生笛卡尔积，因此在where条件中已经有 m.[班级] = a.[班级] 进行保障

Answer 6

看楼主提供的数据集，还有一种情况就是，例如对于A班级可能有这样的数据
班级   分数
A      30
A      30
A      84
A      70
这种情况，如果用DENSE_RANK()就会得到3条数据，即
A      30
A      30
A      84
A      70
用row_number()就不会出现这样的情况，所以3楼的答案存在问题的。1楼和2楼的答案都是正确的

Answer 7

----------------------------------------------------------------
-- Author  :TravyLee(两情若是久长时，又岂在朝朝暮暮！)
-- Date    :2013-08-27 13:28:40
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) 
-- Jul  9 2008 14:17:44 
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：#tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go 
create table #tb([班级] varchar(1),[分数] int)
insert #tb
select 'A',30 union all
select 'A',50 union all
select 'C',52 union all
select 'D',30 union all
select 'A',84 union all
select 'E',68 union all
select 'A',70 union all
select 'B',32
go
 
select
* from #tb a
where
[分数] in(select top 2 [分数] from #tb b where a.班级=b.班级 order by b.分数 asc)
order by 1/*
班级   分数
---- -----------
A    30
A    50
B    32
C    52
D    30
E    68(6 行受影响)
*/
drop table #tb

Answer 8

做排名时，DENSE_RANK() 相同的排序数据有相同的名次，接下来不会跳数
例如
1 30
1 30
2 60
3 90
而RANK()会出现跳数的
1 30
1 30
3 60
4 90是有一点差异的，排名的时候关键是看最后两个名次还是最后两位

Answer 9

SELECT TOP 2 grade,min(score) score FROM scores  GROUP BY grade  ORDER BY score

Answer 10

SQL2005 用Row_Number  + Over子句。。