mysql - 修改MySQL INSERT语句以省略某些行的插入

Question

我试图对上周获得帮助的一份声明进行一些扩展。正如您所看到的，我正在设置一个临时表，并插入来自几十所学校最近进行的测试的学生数据行。插入行时，按分数(totpct_stu，从高到低)排序，并添加 row_number，1 代表最高分数，依此类推。

我了解到#9999学校SMITH的类(class)存在一些问题(每个学生都取得了满分，并且他们是该学区唯一获得满分的学生)。所以，我不想导入 SMITH 的类。

如您所见，我删除了 SMITH 的类(class)，但这扰乱了学校其余学生的行编号(例如，高分 row_number 现在是 20，而不是 1)。

如何修改 INSERT 语句以便不插入此类？

DROP TEMPORARY TABLE IF EXISTS avgpct ;
CREATE TEMPORARY TABLE avgpct_1
   ( sch_code    VARCHAR(3),
     schabbrev   VARCHAR(75),
     teachername VARCHAR(75),
     totpct_stu  DECIMAL(5,1),
     row_number  SMALLINT,
     dummy       VARCHAR(75)
   );
-- ----------------------------------------
INSERT INTO avgpct
SELECT sch_code
     , schabbrev
     , teachername
     , totpct_stu
     , @num := IF( @GROUP = schabbrev, @num + 1, 1 ) AS row_number
     , @GROUP := schabbrev AS dummy
  FROM sci_rpt
 WHERE grade = '05' AND 
       totpct_stu >= 1   -- has a valid score
 ORDER 
    BY sch_code, totpct_stu DESC ;
-- ---------------------------------------
-- select * from avgpct ;
-- ---------------------------------------
DELETE 
  FROM avgpct_1
 WHERE sch_code = '9999' AND 
       teachername = 'SMITH' ;

Answer 1

INSERT INTO avgpct
SELECT sch_code
     , schabbrev
     , teachername
     , totpct_stu
     , @num := IF( @GROUP = schabbrev, @num + 1, 1 ) AS row_number
     , @GROUP := schabbrev AS dummy
  FROM sci_rpt
 WHERE grade = '05' AND 
       totpct_stu >= 1   -- has a valid score
       AND NOT (sch_code = '9999' AND 
       teachername = 'SMITH')
 ORDER 
    BY sch_code, totpct_stu DESC ;