Oracle SQL按照某一列数据去重并显示整行信息（以一列为基准去重）

0.需求背景 

　根据MLOT重复字段 取 TRANSACTIONTIME 最小时间的一列,  (MLOT已经用过group by分组 order by排序)

 所以我们可以用（ROWNUM）伪列函数 +  (MAX)取最大值函数 ||  (MIN)取最小值函数配合 再次使用group by 分组

1.首先把问题表加上伪列（rownum）--记得根据你要分组的列排序一下

<span style="color: #008080;">--</span><span style="color: #008080;">表记得改你自己的--   请参考 select rownum rn，a.* from 你的表 a</span><span style="color: #008080;">
/*</span><span style="color: #008080;"> Formatted on 2020/4/12 9:37:03 (QP5 v5.256.13226.35538) </span><span style="color: #008080;">*/</span>
<span style="color: #0000ff;">SELECT</span> ROWNUM RN, a.<span style="color: #808080;">*</span>
  <span style="color: #0000ff;">FROM</span> (  <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>
            <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> epidm.B_DM_MMS_PVD_INOUT
           </span><span style="color: #0000ff;">WHERE</span> MLOT <span style="color: #808080;">IN</span> (<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931509</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                          </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931510</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                          </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931601</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                          </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931602</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                          </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931610</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                          </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931611</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
        </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> MLOT) a

 2.然后再根据用过伪列的sql当作一张表进行子查询

 <span style="color: #008080;">--</span><span style="color: #008080;"> 子查询且分组  当然你也可以再GROUP BY MLOT 前面进行 where 筛选                    </span><span style="color: #008080;">
/*</span><span style="color: #008080;"> Formatted on 2020/4/12 9:34:48 (QP5 v5.256.13226.35538) </span><span style="color: #008080;">*/</span>
<span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>
  <span style="color: #0000ff;">FROM</span> (<span style="color: #0000ff;">SELECT</span> ROWNUM rn, a.<span style="color: #808080;">*</span>
          <span style="color: #0000ff;">FROM</span> (  <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>
                    <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> epidm.B_DM_MMS_PVD_INOUT
                   </span><span style="color: #0000ff;">WHERE</span> MLOT <span style="color: #808080;">IN</span> (<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931509</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                                  </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931510</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                                  </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931601</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                                  </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931602</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                                  </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931610</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                                  </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931611</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
                </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> MLOT) a) B
 </span><span style="color: #0000ff;">WHERE</span> rn <span style="color: #808080;">IN</span> (  <span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">MIN</span><span style="color: #000000;"> (rn)
                  </span><span style="color: #0000ff;">FROM</span> (<span style="color: #0000ff;">SELECT</span> ROWNUM rn, a.<span style="color: #808080;">*</span>
                          <span style="color: #0000ff;">FROM</span> (  <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>
                                    <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> epidm.B_DM_MMS_PVD_INOUT
                                   </span><span style="color: #0000ff;">WHERE</span> MLOT <span style="color: #808080;">IN</span> (<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931509</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                                                  </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931510</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                                                  </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931601</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                                                  </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931602</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                                                  </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931610</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">,
                                                  </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">P3DAR1931611</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
                                </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> MLOT) a) B
              </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> MLOT)
              </span>

 W.参考模板

<span style="color: #008080;">/*</span><span style="color: #008080;"> Formatted on 2020/4/12 9:46:14 (QP5 v5.256.13226.35538) </span><span style="color: #008080;">*/</span>
<span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>
  <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> 你的表
 </span><span style="color: #0000ff;">WHERE</span> 伪列名称 <span style="color: #808080;">IN</span> (  <span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">MAX</span><span style="color: #000000;"> (伪列名称)
                            </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> 你的表
                        </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span> 你要分组的列) <span style="color: #008080;">--</span><span style="color: #008080;">也可以进行where 筛选</span>

<span style="color: #008080;">--</span><span style="color: #008080;">首先table 表要有rownum伪列函数 并取个名称  这里我取的是rn</span>
<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> <span style="color: #0000ff;">table</span>
<span style="color: #0000ff;">where</span> rn <span style="color: #808080;">in</span> (<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">Max</span>(rn) <span style="color: #0000ff;">from</span> <span style="color: #0000ff;">table</span> <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> mlot)

Oracle SQL按照某一列数据去重并显示整行信息（以一列为基准去重）

标签：out   format   max   子查询   nbsp   text   rownum   等等   sele