问个关于统计的题目，解决即结帖
----------一条SQL语句求解---------
表TBL
NAME GRADE
张三 100
张三 100
张三 80
张三 60
张三 60
李四 80
李四 60
李四 60
王五 60
...
...
求：统计GRADE为100时人数，统计GRADE为80时人数，统计GRADE为60时人数
限制：1：每个人只允许参与一次统计，如张三2条记录GRADE为100但只统计一条
2：若张三在GRADE为100被统计，则在GRADE为80和60时都不被统计（GRADE为80和60时不考虑张三）。

[解决办法]

SQL code

select count(1)  from (select distinct name, grade from tbl where grade = 100);select count(1)  from (select distinct name, grade          from tbl a         where a.grade = 80           and not exists (select name                  from tbl b                 where grade = 100                   and a.name = b.name));select count(1)  from (select distinct name, grade          from tbl a         where a.grade = 60           and not exists (select name                  from tbl b                 where grade in (100,80)                   and a.name = b.name));
[解决办法]
select grade,count(1) num
from  
(select name,max(grade) grade from tbl group by name) A
group by grade;
[解决办法]
with tbl as (
   select 'a' name ,100 grade from dual
   union
   select 'a' name ,80 grade from dual
   union
   select 'b' name ,60 grade from dual
  
)select sum(case a.GRADE when 100 then 1 else 0 end) ,sum(case a.GRADE when 80 then 1 else 0 end),sum(case a.GRADE when 60 then 1 else 0 end)
from
(select max(GRADE) GRADE,NAME
  from tbl
 group by NAME

) a
[解决办法]
探讨

select grade,count(1) num
from
(select name,max(grade) grade from tbl group by name) A
group by grade;