取分组最大值记录代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】取分组最大值记录
作者:Dezai.CN / 发布于2012/7/16/ 874
取分组最大值记录
<div>
<div>子查询：</div>
<span style="font: 12px/18px verdana, arial, helvetica; color: rgb(0, 0, 0); text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(223, 223, 223); -webkit-text-size-adjust: auto; -webkit-text-decorations-in-effect: none; -webkit-text-stroke-width: 0px;">select * from 表 where （序号,名称） in （select max(序号),名称 from 表 group by 名称)</span>

分析函数：

<span style="font: 12px/18px verdana, arial, helvetica; color: rgb(0, 0, 0); text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(241, 241, 241);">select 序号   ,       名称     ,  数量 from <br style="word-wrap: break-word;" />
</span><span style="font: 12px/18px verdana, arial, helvetica; color: rgb(0, 0, 0); text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(241, 241, 241);">(select    序号   ,       名称     ,  数量 <br style="word-wrap: break-word;" />
</span><span style="font: 12px/18px verdana, arial, helvetica; color: rgb(0, 0, 0); text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(241, 241, 241);">,row_number() over(partition by 名称 order by 序号  desc ) rn<br style="word-wrap: break-word;" />
</span><span style="font: 12px/18px verdana, arial, helvetica; color: rgb(0, 0, 0); text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(241, 241, 241);">form tab_name )<br style="word-wrap: break-word;" />
</span><span style="font: 12px/18px verdana, arial, helvetica; color: rgb(0, 0, 0); text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(241, 241, 241);">where rn=1<br style="word-wrap: break-word;" />
<br style="word-wrap: break-word;" />
</span><span style="font: 12px/18px verdana, arial, helvetica; color: rgb(0, 0, 0); text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(241, 241, 241);">或<br style="word-wrap: break-word;" />
</span>
<span style="font: 12px/18px verdana, arial, helvetica; color: rgb(0, 0, 0); text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(241, 241, 241);">select 序号   ,       名称     ,  数量 from <br style="word-wrap: break-word;" />
</span><span style="font: 12px/18px verdana, arial, helvetica; color: rgb(0, 0, 0); text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(241, 241, 241);">(select    序号   ,       名称     ,  数量 <br style="word-wrap: break-word;" />
</span><span style="font: 12px/18px verdana, arial, helvetica; color: rgb(0, 0, 0); text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(241, 241, 241);">, max(序号) over(partition by 名称) rn<br style="word-wrap: break-word;" />
</span><span style="font: 12px/18px verdana, arial, helvetica; color: rgb(0, 0, 0); text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(241, 241, 241);">form tab_name )<br style="word-wrap: break-word;" />
</span>
<span style="font: 12px/18px verdana, arial, helvetica; color: rgb(0, 0, 0); text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(241, 241, 241); -webkit-text-size-adjust: auto; -webkit-text-decorations-in-effect: none; -webkit-text-stroke-width: 0px;">where rn=序号</span>
注意：max的字段只能是number类型字段，如果是date类型的，会提示错误。date类型用上面的row_number()来做就可以了。
<hr width="100%" size="2" />
<div><span style="font: bold 14px/26px Arial; text-align: left; color: rgb(46, 59, 0); text-transform: none; text-indent: 8px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(238, 235, 231); -webkit-text-size-adjust: auto; -webkit-text-decorations-in-effect: none; -webkit-text-stroke-width: 0px;">Oracle 分组 取第一条记录</span></div>
<div><span style="font: 14px/18px Arial; color: rgb(102, 102, 102); text-transform: none; text-indent: 0px; letter-spacing: normal; word-spacing: 0px; white-space: normal; orphans: 2; widows: 2; font-size-adjust: none; font-stretch: normal; background-color: rgb(238, 235, 231); -webkit-text-size-adjust: auto; -webkit-text-decorations-in-effect: none; -webkit-text-stroke-width: 0px;">id        apply_id<br style="line-height: normal;" />
1         1<br style="line-height: normal;" />
2         1<br style="line-height: normal;" />
3         1<br style="line-height: normal;" />
4         2<br style="line-height: normal;" />
5         2<br style="line-height: normal;" />
6         3<br style="line-height: normal;" />
7         3<br style="line-height: normal;" />
8         3<br style="line-height: normal;" />
<br style="line-height: normal;" />
取出<br style="line-height: normal;" />
id        apply_id<br style="line-height: normal;" />
3         1<br style="line-height: normal;" />
5         2<br style="line-height: normal;" />
8         3<br style="line-height: normal;" />
<br style="line-height: normal;" />
select alx_a.id<br style="line-height: normal;" />
from <br style="line-height: normal;" />
(select id,apply_id,rownum rid from 表) alx_a,<br style="line-height: normal;" />
(select id,apply_id,rownum rid from 表) alx_b<br style="line-height: normal;" />
where alx_a.apply_id = alx_b.apply_id and alx_a.id <= alx_b.id <br style="line-height: normal;" />
group by alx_a.id,alx_a.apply_id<br style="line-height: normal;" />
having count(*) = 1</span></div>
</div>
试试其它关键字
　分组最大值记录　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)