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

Question

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

我了解到学校 #9999 在 SMITH 的班级中存在一些问题（每个学生都取得了满分，他们是该学区唯一获得满分的学生）。所以，我不想导入 SMITH 的课程。

正如您所看到的，我删除了 SMITH 的课程，但这搞乱了学校其余学生的行编号（例如，高分 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 ;

Answer 2

沿着这些思路的一些东西可能会起作用。在您的测试环境中，将其更改

WHERE grade = '05' AND 
       totpct_stu >= 1   -- has a valid score

为此

WHERE grade = '05' AND 
       totpct_stu >= 1  AND NOT -- has a valid score
       (sch_code = '9999' AND teachername = 'SMITH')

但是，如果我是您，我预计排除会成为一个反复出现的问题。也许不频繁，但经常出现。这建议采用基于表的方法——将要排除的内容存储在表中，而不是将它们“存储”在 SQL 语句中。