如何取分组的TOPN记录

hnwzhb 2010-10-28 12:55:22
CI AssessedValueUP AssessedValueDW
99991 45.973 43.262
99991 47.862 43.262
99991 62.436 74.352
99991 47.098 55.342
99991 80.94 57.972
99992 53.856 62.436
99992 44.663 45.576
99992 52.055 41.401
99992 229.23 36.768
99992 36.768 53.856

如上面数据,我想取按CI分组,AssessedValueUP与AssessedValueDW排序的TOP3,如何实现,谢谢
...全文
127 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
dawugui 2010-10-28
create table tb(CI varchar(10),AssessedValueUP decimal(18,2),AssessedValueDW decimal(18,2))
insert into tb values('99991', 45.973 ,43.262)
insert into tb values('99991', 47.862 ,43.262)
insert into tb values('99991', 62.436 ,74.352)
insert into tb values('99991', 47.098 ,55.342)
insert into tb values('99991', 80.94 ,57.972)
insert into tb values('99992', 53.856 ,62.436)
insert into tb values('99992', 44.663 ,45.576)
insert into tb values('99992', 52.055 ,41.401)
insert into tb values('99992', 229.23 ,36.768)
insert into tb values('99992', 36.768 ,53.856)
go

--sql 2000
select CI ,AssessedValueUP, AssessedValueDW from
(
select t.* , px=(select count(1) from tb where CI = t.CI and (AssessedValueUP < t.AssessedValueUP or (AssessedValueUP = t.AssessedValueUP and AssessedValueDW < t.AssessedValueDW)) ) + 1 from tb t
) m
where px <= 3


drop table tb

/*

CI AssessedValueUP AssessedValueDW
---------- -------------------- --------------------
99991 45.97 43.26
99991 47.86 43.26
99991 47.10 55.34
99992 44.66 45.58
99992 52.06 41.40
99992 36.77 53.86

(所影响的行数为 6 行)
*/



create table tb(CI varchar(10),AssessedValueUP decimal(18,2),AssessedValueDW decimal(18,2))
insert into tb values('99991', 45.973 ,43.262)
insert into tb values('99991', 47.862 ,43.262)
insert into tb values('99991', 62.436 ,74.352)
insert into tb values('99991', 47.098 ,55.342)
insert into tb values('99991', 80.94 ,57.972)
insert into tb values('99992', 53.856 ,62.436)
insert into tb values('99992', 44.663 ,45.576)
insert into tb values('99992', 52.055 ,41.401)
insert into tb values('99992', 229.23 ,36.768)
insert into tb values('99992', 36.768 ,53.856)
go

--sql 2005
select CI ,AssessedValueUP, AssessedValueDW from
(
select t.* , px=row_number() over(partition by CI order by AssessedValueUP, AssessedValueDW) from tb t
) m
where px <= 3



drop table tb

/*

CI AssessedValueUP AssessedValueDW
---------- --------------------------------------- ---------------------------------------
99991 45.97 43.26
99991 47.10 55.34
99991 47.86 43.26
99992 36.77 53.86
99992 44.66 45.58
99992 52.06 41.40

(6 行受影响)
*/
回复
abuying 2010-10-28
select * from tb a
where exists
(select top 3* from tb b where b.CI=a.CI and a.AssessedValueUP=b.AssessedValueUP and a.AssessedValueDW and b.AssessedValueDW order by AssessedValueUP, AssessedValueDW)
回复
dawugui 2010-10-28
--sql 2000
select CI ,AssessedValueUP, AssessedValueDW from
(
select t.* , px=(select count(1) from tb where CI = t.CI and (AssessedValueUP < t.AssessedValueUP or (AssessedValueUP = t.AssessedValueUP and AssessedValueDW < t.AssessedValueDW)) ) + 1 from tb t
) m
where px <= 3

--sql 2005
select CI ,AssessedValueUP, AssessedValueDW from
(
select t.* , px=row_number() over(partition by CI order by AssessedValueUP, AssessedValueDW) from tb t
) m
where px <= 3
回复
heymal 2010-10-28
select * from tb a
where exists
(select top 3* from tb b where b.CI=a.CI order by AssessedValueUP, AssessedValueDW)
回复
guguda2008 2010-10-28
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY CT ORDER BY AssessedValueUP,AssessedValueDW) AS NID,*
FROM TB
) T WHERE NID<=3
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-28 12:55
社区公告
暂无公告