代码之家 › 专栏 › 技术社区 › kkk12a9

根据其他表中的数据更新和更新数据

sqlite database sql

kkk12a9 · 技术社区 · 3 年前

有三张桌子 student , course 和 takes 如下

CREATE TABLE student
(
     ID         varchar(5), 
     name       varchar(20) NOT NULL, 
     dept_name  varchar(20), 
     tot_cred   numeric(3,0) CHECK (tot_cred >= 0),
     PRIMARY KEY (ID),
     FOREIGN KEY (dept_name) REFERENCES department
        ON DELETE SET NULL
)

CREATE TABLE takes
(
     ID         varchar(5), 
     course_id  varchar(8),
     sec_id     varchar(8), 
     semester   varchar(6),
     year       numeric(4,0),
     grade      varchar(2),
     PRIMARY KEY (ID, course_id, sec_id, semester, year),
     FOREIGN KEY (course_id, sec_id, semester, year) REFERENCES section
        ON DELETE CASCADE,
     FOREIGN KEY (ID) REFERENCES student
        ON DELETE CASCADE
    )

CREATE TABLE course
(
    course_id   varchar(8), 
    title       varchar(50), 
    dept_name   varchar(20),
    credits     numeric(2,0) CHECK (credits > 0),

    PRIMARY KEY (course_id),
    FOREIGN KEY (dept_name) REFERENCES department
        ON DELETE SET NULL
)

tot_cred 中的列数据 大学生 表现在被分配了随机值(不正确),我想执行查询,根据课程的设置更新和更新这些数据 grade 每个学生都参加了考试。对于那些收到 F 成绩将被排除在外,未参加任何课程的学生将被分配为0分 托特克鲁德 .

我想出了两种方法,一种是

UPDATE student 
SET tot_cred = (SELECT SUM(credits)  
                FROM takes, course  
                WHERE takes.course_id = course.course_id 
                  AND student.ID = takes.ID 
                  AND takes.grade <> 'F' 
                  AND takes.grade IS NOT NULL)

这个查询满足了我的所有需求,但对于那些没有参加任何课程的学生来说,它会指定空值,而不是0。

第二是使用 case when

UPDATE student
SET tot_cred = (select sum(credits)
    case 
         when sum(credits) IS NOT NULL then sum(credits)
         else 0 end
    FROM takes as t, course as c
    WHERE t.course_id = c.course_id
    AND t.grade<>'F' and t.grade IS NOT NULL
)

但它给所有学生分配了0。有没有办法达到上述要求?

1 回复 | 直到 3 年前

forpas 3 年前

如果第一个查询满足您的要求,唯一的问题是它返回 NULL 对于没有参加任何课程的学生来说,最简单的解决方案是使用而不是 SUM() 聚合函数函数 TOTAL() 它会回来的 0 而不是 无效的 :

UPDATE student AS s 
SET tot_cred = (
  SELECT TOTAL(c.credits)  
  FROM takes t INNER JOIN course c 
  ON t.course_id = c.course_id 
  WHERE t.ID = s.ID AND t.grade <> 'F' AND t.grade IS NOT NULL
);

同样的道理也可以用 COALESCE() :

SELECT COALESCE(SUM(credits), 0)...

此外,使用正确的连接和 ON 子句和别名,以提高可读性。