有表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 dualunion allselect 'A',2 from dualunion allselect 'A',3 from dualunion allselect 'B',1 from dualunion allselect 'C',1 from dualunion allselect 'C',2 from dualunion allselect '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 tidTID FLG C--- ---------- ----------A 3 1B 1 1C 2 2