sql分组排名

数据库teradata

--班级科目成绩表

CREATE MULTISET TABLE PD_PORTAL.aaa ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      banji VARCHAR(40) CHARACTER SET LATIN CASESPECIFIC TITLE '班级',
      kecheng VARCHAR(40) CHARACTER SET LATIN CASESPECIFIC TITLE '科目',     
      ceshiren VARCHAR(40) CHARACTER SET LATIN CASESPECIFIC TITLE '测试人',      
      chengji INT TITLE '成绩')

 

--测试数据

 INSERT INTO PD_PORTAL.aaa VALUES('110','语文','张三',80);
 INSERT INTO PD_PORTAL.aaa VALUES('110','数学','张三',70);
 INSERT INTO PD_PORTAL.aaa VALUES('110','英语','张三',60);
 INSERT INTO PD_PORTAL.aaa VALUES('110','语文','李四',80);
 INSERT INTO PD_PORTAL.aaa VALUES('110','数学','李四',70);
 INSERT INTO PD_PORTAL.aaa VALUES('110','英语','李四',60);     
 INSERT INTO PD_PORTAL.aaa VALUES('110','语文','王五',80);
 INSERT INTO PD_PORTAL.aaa VALUES('110','数学','王五',70);
 INSERT INTO PD_PORTAL.aaa VALUES('110','英语','王五',60);

--count sql

 SELECT * FROM PD_PORTAL.aaa a WHERE (SELECT COUNT(*) FROM PD_PORTAL.aaa b WHERE a.banji=b.banji AND b.kecheng=a.kecheng
    AND a.chengji<b.chengji)<2

--sql 分析函数

   SELECT a.banji,a.kecheng,a.ceshiren,a.chengji
  ,rank() over (partition BY a.banji,a.kecheng ORDER BY a.banji,a.kecheng,a.chengji  DESC) AS rank_1
  FROM PD_PORTAL.aaa a
  qualify rank_1<3