如何将数据分组并只取每组前N条

Question

表结构
A月份　　　B姓名     C工资　　
200801　　　张三　　　1000.00
200801　　　李四　　　1500.00
200801　　　王五　　　1300.00
200802　　　张三　　　1050.00
200802　　　李四　　　1600.00
200802　　　王五　　　1380.00
200802　　　赵六　　　1300.00
200803.....
200804.....现要统计每个月工资排列前N位的记录，希望一条语句搞定，谢谢！！

Answer 1

MARK一下，这个应用很常见，等解答

Answer 2

--> liangCK小梁 于2008-10-22
 
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (A月份 INT,B姓名 VARCHAR(4),C工资 NUMERIC(6,2))
INSERT INTO #T
SELECT 200801,'张三',1000.00 UNION ALL
SELECT 200801,'李四',1500.00 UNION ALL
SELECT 200801,'王五',1300.00 UNION ALL
SELECT 200802,'张三',1050.00 UNION ALL
SELECT 200802,'李四',1600.00 UNION ALL
SELECT 200802,'王五',1380.00 UNION ALL
SELECT 200802,'赵六',1300.00--SQL查询如下:SELECT DISTINCT b.*
FROM #T AS a
CROSS APPLY
(
  SELECT TOP(2) *
  FROM #T
  WHERE A月份=a.A月份
  ORDER BY C工资 DESC
) AS b/*
A月份         B姓名  C工资
----------- ---- ---------------------------------------
200801      李四   1500.00
200801      王五   1300.00
200802      李四   1600.00
200802      王五   1380.00(4 行受影响)
*/

Answer 3

--> liangCK小梁 于2008-10-22
 
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (A月份 INT,B姓名 VARCHAR(4),C工资 NUMERIC(6,2))
INSERT INTO #T
SELECT 200801,'张三',1000.00 UNION ALL
SELECT 200801,'李四',1500.00 UNION ALL
SELECT 200801,'王五',1300.00 UNION ALL
SELECT 200802,'张三',1050.00 UNION ALL
SELECT 200802,'李四',1600.00 UNION ALL
SELECT 200802,'王五',1380.00 UNION ALL
SELECT 200802,'赵六',1300.00--SQL查询如下:SELECT *
FROM #T AS t
WHERE 2>(SELECT COUNT(*)
         FROM #T
         WHERE A月份=t.A月份
           AND C工资>t.C工资)/*
A月份         B姓名  C工资
----------- ---- ---------------------------------------
200801      李四   1500.00
200801      王五   1300.00
200802      李四   1600.00
200802      王五   1380.00(4 行受影响)
*/

Answer 4

--declare @t table(A varchar(10),B nvarchar(10), C  money)　　 
DECLARE @T TABLE(a VARCHAR(10),B NVARCHAR(10), C MONEY)
insert @t select '200801',N'张三',1000.00 
INSERT @T SELECT '200801',N'李四',1500.00 
INSERT @T SELECT '200801',N'王五',1300.00 
INSERT @T SELECT '200802',N'张三',1050.00 
INSERT @T SELECT '200802',N'李四',1600.00 
INSERT @T SELECT '200802',N'王五',1380.00 
INSERT @T SELECT '200802',N'赵六',1300.00 
SELECT * FROM @T T WHERE CHECKSUM(*) IN (SELECT TOP 2 CHECKSUM(*) FROM @T WHERE A=T.A ORDER BY C DESC )
/*a          B          C                     
---------- ---------- --------------------- 
200801     李四         1500.0000
200801     王五         1300.0000
200802     李四         1600.0000
200802     王五         1380.0000(影響 4 個資料列)*/

Answer 5

/**前两位
CREATE TABLE #A (S_MONTH VARCHAR(10),NAME VARCHAR(10) ,NUM INT)
INSERT #A  SELECT '200801','333',1000
INSERT #A  SELECT '200801','444',1500
INSERT #A  SELECT '200801','555',1300
INSERT #A  SELECT '200802','333',1050
INSERT #A  SELECT '200802','444',1600
INSERT #A  SELECT '200802','555',1380
INSERT #A  SELECT '200803','333',1300
SELECT * FROM #A A
WHERE (SELECT COUNT(*) FROM #A WHERE S_MONTH=A.S_MONTH AND NUM>A.NUM)<2
        DROP TABLE #A 

Answer 6

--> --> 
 --用rank/dense_rank
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([A月份] nvarchar(6),[B姓名] nvarchar(2),[C工资] decimal(18,2))
Insert #T
select '200801',N'张三',1000.00 union all
select '200801',N'李四',1500.00 union all
select '200801',N'王五',1300.00 union all
select '200802',N'张三',1050.00 union all
select '200802',N'李四',1600.00 union all
select '200802',N'王五',1380.00 union all
select '200802',N'赵六',1300.00
Go
select 
[A月份],[B姓名],[C工资]
from 
(Select *,row=dense_rank()over(partition by [A月份] order by [C工资] desc) from #T a)t
where
row<=2
(7 個資料列受到影響)
A月份    B姓名  C工资
------ ---- ---------------------------------------
200801 李四   1500.00
200801 王五   1300.00
200802 李四   1600.00
200802 王五   1380.00(4 個資料列受到影響)

Answer 7

我们经常会有这样的需求，即按照地区来分别取出每个地区排名前3的那些记录。本文总结了几种方法，希望大家补充。首先，创建测试用的表和数据，如下：create table test
(
areaid int,
score int
)
insert into test select 0,10
union all select 0,20
union all select 0,30
union all select 0,40
union all select 0,50
union all select 1,10
union all select 1,20
union all select 1,30
union all select 1,40
union all select 1,50
union all select 2,10
union all select 2,20
union all select 2,30
union all select 2,40
union all select 2,50
go第一种方法适用于sql2000和2005，其代码如下：select * from test a
where checksum(*) in (select top 3 checksum(*) from test b where a.areaid=b.areaid order by score desc)第二种方法是利用sql2005的函数ROW_NUMBER，其代码如下：WITH test1 AS
(
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY areaid ORDER BY score desc) AS 'RowNumber'
    FROM test 
) 
SELECT * 
FROM test1 
WHERE RowNumber BETWEEN 1 AND 3;第三种方法是利用sql2005的cross apply来实现，其代码如下：select  distinct t.* from test a
cross apply
(select top 3 areaid,score from test
where a.areaid=areaid order by score desc) as T