关于group by 的问题

Question

假设有如下表，表名为 T：  有3个字段 F1，F2，F2
F1           F2  F3
--------------------------
2008-01-01   1   A
2008-01-01   2   B
2008-02-10   1   B
2008-02-10   2   A需要按F1字段分组，F2字段取最大值，  对F3字段，要求取F2字段最大的值的哪条记录的值。
即结果如下：
2008-01-01   2   B
2008-02-10   2   A      
这样的SQL应如何写?

Answer 1

select f1,max(f2),f3
from t
group by f1,f3 

Answer 2

楼上的错误
select tt.*
from
(
select t.* ,row_number() over (partition by t.f1 order by t.f2 desc) rn
   from t) tt
where tt.rn=2

Answer 3

写错了，应该rn=1
SELECT tt.*
  FROM (SELECT t.*,
               ROW_NUMBER () OVER (PARTITION BY t.f1 ORDER BY t.f2 DESC) rn
          FROM t) tt
 WHERE tt.rn = 1

Answer 4

select f1,max(f2),f3 
from t 
group by f1,f3 
你这取出来的结果是这样的
F1           F2  F3 
-------------------------- 
2008-01-01   1   A 
2008-01-01   2   B 
2008-02-10   1   B 
2008-02-10   2   A 

Answer 5

先试一下，hebo2005 的方法。

Answer 6

提供另一种方法：
select  t.f1,t.f2,t.f3
from t,
        (
             select f1 ,max(f2) as f2  from t group by f1
          )q
where t.f1=q.f1
and t.f2=q.f2

Answer 7

一种笨办法，用存储过程：使用临时表 aa(f1,F2,F3)加游标
insert into aa(F1) select f1 from t group by f1;
CURSOR curT IS 
    SELECT f1 FROM aa;
curTRow curT%ROWTYPE;CURSOR curInner(f1T varchar2) IS
    SELECT MAX(f2),f3 FROM t WHERE t.f1 = f1T;
curInnerRow curInner%ROWTYPE;OPEN curT;
LOOP
   FETCH curT INTO curTRow;
   EXIT WHEN curT%NOTFOUND;
   OPEN curInner(curTRow.f1);
   LOOP
     FETCH curInner INTO curInnerRow;
     EXIT WHEN curInner%NOTFOUND;
     UPDATE aa SET F2 = curInnerRow.f2,f3 = curInnerRow.f3 WHERE f1 = curTRow.f1;
   LOOP;
END LOOP;
COMMIT;
OPEN curR for
   SELECT * FROM aa;---就是最后结果核心过程，直接写的没有测试。  

Answer 8

SELECT tt.*
  FROM (SELECT t.*,
               ROW_NUMBER () OVER (PARTITION BY t.f1 ORDER BY t.f2 DESC) rn
          FROM t) tt
 WHERE tt.rn = 1

Answer 9

select * from t_test tt where tt.f2 in( select max(t.f2) from t_test t group by f1);
这个也行.
hebo2005,为什么喜欢用row_number。

Answer 10

给你看你两种语句的执行计划，你就知道为什么了
我的表名tcode
有 14个字段，4431条纪录
其中code_lgroup和code_mgroup都是pk
取每个code_lgroup 中code_mgroup 最大的那条纪录
结果都是357条纪录
方法一select aa.*
from(
SELECT a.*,
       ROW_NUMBER () OVER (PARTITION BY code_lgroup ORDER BY code_mgroup DESC)
                                                                           rn
  FROM tcode a) aa
  where aa.rn=1执行计划Plan
SELECT STATEMENT  ALL_ROWSCost: 15  Bytes: 18,957,510  Cardinality: 4,419  
3 VIEW EASYTVC. Cost: 15  Bytes: 18,957,510  Cardinality: 4,419  
2 WINDOW SORT PUSHED RANK  Cost: 15  Bytes: 212,112  Cardinality: 4,419  
1 TABLE ACCESS FULL TABLE EASYTVC.TCODE Cost: 13  Bytes: 212,112  Cardinality: 4,419  方法2SELECT tt.*
  FROM tcode tt,
       (SELECT   code_lgroup, MAX (code_mgroup) code_mgroup
            FROM tcode
        GROUP BY code_lgroup) t1
 WHERE tt.code_lgroup = t1.code_lgroup AND tt.code_mgroup = t1.code_mgroup
执行计划
Plan
SELECT STATEMENT  ALL_ROWSCost: 22  Bytes: 23,852  Cardinality: 356  
5 HASH JOIN  Cost: 22  Bytes: 23,852  Cardinality: 356  
3 VIEW EASYTVC. Cost: 8  Bytes: 6,764  Cardinality: 356  
2 HASH GROUP BY  Cost: 8  Bytes: 3,560  Cardinality: 356  
1 INDEX FAST FULL SCAN INDEX (UNIQUE) EASYTVC.PK_TCODE Cost: 6  Bytes: 44,190  Cardinality: 4,419  
4 TABLE ACCESS FULL TABLE EASYTVC.TCODE Cost: 13  Bytes: 212,112  Cardinality: 4,419  

Answer 11

算了下，两种算法成本差不多
刚刚用张大表试了下，有18万的数据，结果有3万6，
结果算下来还是MAX的略快点
不过从执行计划来看，第一种方法只要做一次全表扫描，主要慢在order by上面
第二种方法至少一次全表扫描，（如果MAX字段不是索引的话，要两次了）
所以如果服务器的IO性能不好的话，会花在这里的时间比较多，可能会出现执行成本比第一种低，但执行时间比较第一种高的情况这两种方法随各人喜好了

Answer 12

sql_plus的话
set autotrace on
然后运行sql，就可以出来执行计划
TOAD的话，直接运行小救护车就可以得出执行计划我昨天另外了个用exists的语句来执行，结果看执行计划，exists的成本最高，接下来是MAX的，然后是row_number的
但从对一张大表的执行时间来看，exists<max<row_number
不过我发现得出来的纪录数不一样，exist=max>row_number，不知道怎么回事，我有空查查，我测试另一张小表的，数据是一样的，
估计max为什么要比exist执行计划小，但耗时多，我想主要是因为有二次IO读写，所以慢了（执行计划成本<>执行时间）
从我昨天做的实验来说，对大表来说效率上应该还是max快点
不过我觉得row_number灵活点
楼主的题目现在是只要f2最大的一条，所以row_number和max都可以如果要求是取前几条，比如说前10条，那就能体现row_number的优势了
语句直接改成rn<=10就行了