假设有一个表tableX,三个字段联合唯一,假设为fieldA varchar(64),fieldB number,fieldC number,并且有个无意义主键假设为pid,现在我想查出在同一个fieldA下首先fieldB最大,然后fieldC最大的那一条记录的pid,我现在有一个查询语句可以查到,但效率可能存在问题,请问如何查才是最高效?
假设数据如下:
pid  fieldA fieldB fieldC 
 1     xxx    1      1
 2     xxx    1      2
 3     xxx    2      1
 4     yyy    1      1
 5     yyy    1      2
 6     yyy    1      3
 7     yyy    2      1
 8     yyy    2      2  
其中pid为3和8的记录是我想要的,我自己写的查询语句如下:
SELECT pid
  FROM tablex ttt,
       (SELECT   t2.fielda, t2.fieldb, MAX (fieldc) AS fieldc
            FROM tablex t2,
                 (SELECT   fielda, MAX (fieldb) AS fieldb
                      FROM tablex
                  GROUP BY fielda) tt       --tt为同一fieldA下fieldB最大的一条
           WHERE t2.fielda = tt.fielda AND t2.fieldb = tt.fieldb
        GROUP BY t2.fielda, t2.fieldb) t3
--t3是同一fieldA下fieldB最大且fieldC最大的一条,因为使用group by,不能查询到pid,所以和自身关联查询获得pid
 WHERE t3.fielda = ttt.fielda
   AND t3.fieldb = ttt.fieldb
   AND t3.fieldc = ttt.fieldc

解决方案 »

  1.   


    SELECT pid
      FROM tablex ttt,
           (SELECT   t2.fielda, t2.fieldb, MAX (fieldc) AS fieldc
                FROM tablex t2,
                     (SELECT   fielda, MAX (fieldb) AS fieldb
                          FROM tablex
                      GROUP BY fielda) tt       --tt为同一fieldA下fieldB最大的一条
               WHERE t2.fielda = tt.fielda AND t2.fieldb = tt.fieldb
            GROUP BY t2.fielda, t2.fieldb) t3
    --t3是同一fieldA下fieldB最大且fieldC最大的一条,因为使用group by,不能查询到pid,所以和自身关联查询获得pid
     WHERE t3.fielda = ttt.fielda
       AND t3.fieldb = ttt.fieldb
       AND t3.fieldc = ttt.fieldc 
      

  2.   


    with t1 as
    (
         select 1 pid,'xxx' fieldA,1 fieldB,1 fieldC from dual
         union all
         select 2 pid,'xxx' fieldA,1 fieldB,2 fieldC from dual
         union all
         select 3 pid,'xxx' fieldA,2 fieldB,1 fieldC from dual
         union all
         select 4 pid,'xxx' fieldA,1 fieldB,1 fieldC from dual
         union all
         select 5 pid,'yyy' fieldA,1 fieldB,2 fieldC from dual
         union all
         select 6 pid,'yyy' fieldA,1 fieldB,3 fieldC from dual
         union all
         select 7 pid,'yyy' fieldA,2 fieldB,1 fieldC from dual
         union all
         select 8 pid,'yyy' fieldA,2 fieldB,2 fieldC from dual
    )select pid,fieldA,fieldB,fieldC
    from 
    (
    select t1.*,row_number() over(partition by fieldA order by fieldB desc,fieldC desc) rn 
    from t1
    )
    where rn = 1    pid    fieldA fieldB fieldC
    --------------------------------------
    1 3 xxx 2 1
    2 8 yyy 2 2
      

  3.   

    非常感谢,菜鸟表示折服。我先看看这些over, partition这些关键字都是什么意思。