日期:2014-05-17  浏览次数:20808 次

分组筛选SQL语句求教
有表A

TID FLG
-----------
 A 1
 A 2
 A 3
 B 1
 C 1
 C 2
 C 2

按TID、FLG分组统计,只显示FLG最大的那组,FLG最大值是多少不确定
结果如下

TID FLG COUNT(*)
---------------------
 A 3 1
 B 1 1
 C 2 2

试了用HAVING做不到,求帮助。。。。。


------解决方案--------------------
SQL code
with t as(
select 'A' tid,1 flg from dual
union all
select 'A',2 from dual
union all
select 'A',3 from dual
union all
select 'B',1 from dual
union all
select 'C',1 from dual
union all
select 'C',2 from dual
union all
select 'C',2 from dual
)
select tid, flg, count(1) c
  from t t1
 where not exists (select 1
          from t t2
         where t1.tid = t2.tid
           and t2.flg > t1.flg)
 group by tid, flg order by tid

TID        FLG          C
--- ---------- ----------
A            3          1
B            1          1
C            2          2