如何取分组的TOPN记录

2010-10-28 12:55:22 203 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

告别命令行！用这个开源的Web版Kettle工具，5分钟搞定数据集成任务编排本文介绍了一款开源的Web版Kettle工具，通过直观的拖拽界面大幅提升数据集成效率，告别复杂的命令行操作。该工具基于Kettle核心引擎，支持跨平台协作、轻量化访问和降低学习曲线，特别适合业务分析师快速上手。实测显示，使用该工具后任务配置错误率下降62%，协作效率提升40%。阅读详情

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，如何实现，谢谢

...全文

C++详解二叉树的三种遍历方式上一篇文章，讲了二叉树的创建——C++实现链表存储二叉树接下来讲解二叉树的三种遍历方式：先序(前序)遍历、中序遍历、后序遍历，三种方式都是DFS(Depth First Search)深度优先搜索，核心思想都是函数的递归(栈)，如果你能理解上面这篇文章中创建二叉树的具体过程，那么遍历就轻而易举 1.先序遍历 Former_Order_Traversel(BinTree_Node* p) { ... 阅读详情

hnwzhb

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

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

如何取分组的TOPN记录

相关推荐