时间戳

首页 > 解决方案 > 需要选择查询来查找空值

sql - 需要选择查询来查找空值

问题描述

学生 id = 4 在课程 id = 20 上没有值，如何使用选择查询显示它？预期的输出是包含学生详细信息的一行这是我的 SQL 代码：

create table Students(
    student_id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    lname varchar(30) NOT NULL,
    fname varchar(30) NOT NULL,
    age int NULL CHECK (age > 18),
    phone char(13) NULL CHECK (phone LIKE '[(][0-9][0-9][0-9][)][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]')
);

-- 创建课程表：

create table Course(
    course_id int IDENTITY(10,10) NOT NULL PRIMARY KEY,
    title varchar(50) NULL,
    nb_hrs int NULL
);

-- 创建成绩表：

create table Grades(
    course_id int NOT NULL FOREIGN KEY REFERENCES Course(course_id),
    student_id int NOT NULL FOREIGN KEY REFERENCES Students(student_id),
    grade float NOT NULL CHECK (grade BETWEEN 0 AND 100)
);

-- 向表中插入数据：

INSERT INTO students(lname, fname, age) VALUES ('Salard', 'Jean', 19)
INSERT INTO students(lname, fname, age) VALUES ('Le Bert', 'Antoine', 24)
INSERT INTO students(lname, fname, age) VALUES ('Legendre', 'Pierre', 23)
INSERT INTO students(lname, fname, age) VALUES ('Turcotte', 'Cedric', 24)
INSERT INTO students(lname, fname, age) VALUES ('Tremblay', 'Alfredo', 22)
INSERT INTO students(lname, fname, age) VALUES ('Dominguez', 'Silvia', 21)
INSERT INTO students(lname, fname, age) VALUES ('Wright', 'Michelle', 30)
INSERT INTO students(lname, fname, age) VALUES ('Sullivan', 'Anne', 20)
INSERT INTO students(lname, fname, age) VALUES ('Petit-Clair', 'Cyril', 21)
INSERT INTO students(lname, fname, age) VALUES ('Herbert', 'Jean-Marc', 32)

INSERT INTO course(title, nb_hrs) VALUES ('P41', 45)
INSERT INTO course(title, nb_hrs) VALUES ('P42', 60)
INSERT INTO course(title, nb_hrs) VALUES ('P60', 60)
INSERT INTO course(title, nb_hrs) VALUES ('P61', 60)

INSERT INTO grades(course_id, student_id, grade) VALUES (10, 1, 78)
INSERT INTO grades(course_id, student_id, grade) VALUES (40, 1, 85)
INSERT INTO grades(course_id, student_id, grade) VALUES (40, 2, 80)
INSERT INTO grades(course_id, student_id, grade) VALUES (10, 2, 90)
INSERT INTO grades(course_id, student_id, grade) VALUES (30, 1, 84)
INSERT INTO grades(course_id, student_id, grade) VALUES (20, 10, 96)
INSERT INTO grades(course_id, student_id, grade) VALUES (30, 2, 61)
INSERT INTO grades(course_id, student_id, grade) VALUES (40, 9, 89)
INSERT INTO grades(course_id, student_id, grade) VALUES (40, 10, 73)
INSERT INTO grades(course_id, student_id, grade) VALUES (10, 3, 45)
INSERT INTO grades(course_id, student_id, grade) VALUES (40, 5, 80)
INSERT INTO grades(course_id, student_id, grade) VALUES (40, 6, 33)
INSERT INTO grades(course_id, student_id, grade) VALUES (10, 4, 67)
INSERT INTO grades(course_id, student_id, grade) VALUES (20, 3, 84)
INSERT INTO grades(course_id, student_id, grade) VALUES (10, 5, 35)
INSERT INTO grades(course_id, student_id, grade) VALUES (20, 5, 85)
INSERT INTO grades(course_id, student_id, grade) VALUES (20, 7, 80)
INSERT INTO grades(course_id, student_id, grade) VALUES (30, 3, 67)
INSERT INTO grades(course_id, student_id, grade) VALUES (30, 6, 77)
INSERT INTO grades(course_id, student_id, grade) VALUES (30, 7, 80)
INSERT INTO grades(course_id, student_id, grade) VALUES (30, 4, 33)
INSERT INTO grades(course_id, student_id, grade) VALUES (10, 6, 56)
INSERT INTO grades(course_id, student_id, grade) VALUES (10, 7, 87)
INSERT INTO grades(course_id, student_id, grade) VALUES (30, 9, 89)
INSERT INTO grades(course_id, student_id, grade) VALUES (30, 10, 73)
INSERT INTO grades(course_id, student_id, grade) VALUES (20, 1, 89)
INSERT INTO grades(course_id, student_id, grade) VALUES (20, 9, 18)
INSERT INTO grades(course_id, student_id, grade) VALUES (20, 2, 50)
INSERT INTO grades(course_id, student_id, grade) VALUES (30, 5, 79)
INSERT INTO grades(course_id, student_id, grade) VALUES (30, 8, 37)
INSERT INTO grades(course_id, student_id, grade) VALUES (40, 3, 67)
INSERT INTO grades(course_id, student_id, grade) VALUES (40, 4, 77)
INSERT INTO grades(course_id, student_id, grade) VALUES (20, 8, 75)
INSERT INTO grades(course_id, student_id, grade) VALUES (40, 7, 56)
INSERT INTO grades(course_id, student_id, grade) VALUES (40, 8, 87)
INSERT INTO grades(course_id, student_id, grade) VALUES (10, 8, 38)
INSERT INTO grades(course_id, student_id, grade) VALUES (20, 6, 92)
INSERT INTO grades(course_id, student_id, grade) VALUES (10, 9, 79)
INSERT INTO grades(course_id, student_id, grade) VALUES (10, 10, 97)

（这是一些额外的文字，只是为了让我能够在...中编辑代码围栏）

标签： sqlsql-server

解决方案

最终答案：

select s.student_id,s.lname,s.fname,s.age,s.phone from Students s where s.student_id in (select student_id from grades g group by student_id with count(student_id) < 4 )

感谢大家

推荐阅读