studentcoursetakes如下所示

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
)

student表中的tot_cred列数据现在被分配了随机值(不正确),我想执行一个查询,根据课程中每个学生所选的grade列数据更新这些数据.获得F分的学生将被排除在外,没有参加任何课程的学生将被分配0分为tot_cred分.

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

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.有没有办法达到上述要求?

推荐答案

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

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)... 

Also, use a proper join with an ON clause and aliases for the tables to improve readability.

Sql相关问答推荐

SQL使最终结果显示两个表后的所有数据(匹配和不匹配)?

更新在两个或多个面中具有交点的面

基于多个字段删除Access中的重复记录,同时保留最低优先级

为什么在这种情况下我不能使用LAG函数?

通过之前的连接-这是Oracle的错误吗?

按日期时间(不包括秒)连接表

使用DatePart函数对日期时间值进行分组

带上最后日期(结果)

我可以在SQLite3中使用BLOB作为主键吗?

需要使用SQLite查询进行一些奇怪的时间转换

如何判断小数点后千位是否不为0

POSTGRES to_timestamp() 假定 UTC 字符串为本地时间

清理 XML 数据

SQL 将 Varchar 转换为日期

SQL Server: 将JSON对象数组转换为表格格式

SQL 计数和过滤查询优化

如何根据 ID 和指标从 2 个表中找到不同的值?

在 PostgreSQL 中,如何将数组中的每个元素用作另一个表中的键?

SQL 中的问题与包含最大日期的记录连接

当我按 PK 分组时,该表中的所有列在每个组中都具有相同的值.那么为什么 SQL Server 需要对这些列进行聚合呢?