考虑这个关系模式
CREATE TABLE student(
student_id VARCHAR(10),
student_name VARCHAR(20),
PRIMARY KEY (student_id));
CREATE TABLE registered(
student_id VARCHAR(10),
course_id VARCHAR(10),
写出sql查询,列出每个学生的id 和姓名以及该学生注册的总课程数,没有注册任何课程的也要列出,其注册课程总数为0.测试用例:
PRIMARY KEY(student_id,course_id));
INSERT INTO student VALUES('2010551201','Jack');
INSERT INTO student VALUES('2010551202','John');
INSERT INTO student VALUES('2010551203','Peter');
INSERT INTO student VALUES('2010551204','Smith');
INSERT INTO student VALUES('2010551205','Hayes');
INSERT INTO student VALUES('2010551206','Green');
INSERT INTO student VALUES('2010551207','Andrew');
INSERT INTO student VALUES('2010551208','Willimas');
INSERT INTO student VALUES('2010551209','Curry');
INSERT INTO student VALUES('2010551210','Lindsay');
INSERT INTO student VALUES('2010551211','Lily');
INSERT INTO student VALUES('2010551212','Colin');
INSERT INTO student VALUES('2010551213','Corey');
INSERT INTO student VALUES('2010551214','Eric');
INSERT INTO student VALUES('2010551215','Colby');
INSERT INTO registered VALUES('2010551201','01');
INSERT INTO registered VALUES('2010551202','01');
INSERT INTO registered VALUES('2010551202','02');
INSERT INTO registered VALUES('2010551203','02');
INSERT INTO registered VALUES('2010551204','01');
INSERT INTO registered VALUES('2010551204','02');
INSERT INTO registered VALUES('2010551205','04');
INSERT INTO registered VALUES('2010551206','07');
INSERT INTO registered VALUES('2010551207','01');
INSERT INTO registered VALUES('2010551207','03');
INSERT INTO registered VALUES('2010551207','04');
INSERT INTO registered VALUES('2010551208','06');
INSERT INTO registered VALUES('2010551209','07');
INSERT INTO registered VALUES('2010551210','06');
INSERT INTO registered VALUES('2010551211','04');
INSERT INTO registered VALUES('2010551212','01');
INSERT INTO registered VALUES('2010551213','');
INSERT INTO registered VALUES('2010551214','01');
INSERT INTO registered VALUES('2010551215','');我写的sql语句
SELECT student_name,COUNT(course_id)
FROM student s,registered r
WHERE s.student_id=r.student_id
GROUP BY student_name;
结果不对 求大虾指正
CREATE TABLE student(
student_id VARCHAR(10),
student_name VARCHAR(20),
PRIMARY KEY (student_id));
CREATE TABLE registered(
student_id VARCHAR(10),
course_id VARCHAR(10),
写出sql查询,列出每个学生的id 和姓名以及该学生注册的总课程数,没有注册任何课程的也要列出,其注册课程总数为0.测试用例:
PRIMARY KEY(student_id,course_id));
INSERT INTO student VALUES('2010551201','Jack');
INSERT INTO student VALUES('2010551202','John');
INSERT INTO student VALUES('2010551203','Peter');
INSERT INTO student VALUES('2010551204','Smith');
INSERT INTO student VALUES('2010551205','Hayes');
INSERT INTO student VALUES('2010551206','Green');
INSERT INTO student VALUES('2010551207','Andrew');
INSERT INTO student VALUES('2010551208','Willimas');
INSERT INTO student VALUES('2010551209','Curry');
INSERT INTO student VALUES('2010551210','Lindsay');
INSERT INTO student VALUES('2010551211','Lily');
INSERT INTO student VALUES('2010551212','Colin');
INSERT INTO student VALUES('2010551213','Corey');
INSERT INTO student VALUES('2010551214','Eric');
INSERT INTO student VALUES('2010551215','Colby');
INSERT INTO registered VALUES('2010551201','01');
INSERT INTO registered VALUES('2010551202','01');
INSERT INTO registered VALUES('2010551202','02');
INSERT INTO registered VALUES('2010551203','02');
INSERT INTO registered VALUES('2010551204','01');
INSERT INTO registered VALUES('2010551204','02');
INSERT INTO registered VALUES('2010551205','04');
INSERT INTO registered VALUES('2010551206','07');
INSERT INTO registered VALUES('2010551207','01');
INSERT INTO registered VALUES('2010551207','03');
INSERT INTO registered VALUES('2010551207','04');
INSERT INTO registered VALUES('2010551208','06');
INSERT INTO registered VALUES('2010551209','07');
INSERT INTO registered VALUES('2010551210','06');
INSERT INTO registered VALUES('2010551211','04');
INSERT INTO registered VALUES('2010551212','01');
INSERT INTO registered VALUES('2010551213','');
INSERT INTO registered VALUES('2010551214','01');
INSERT INTO registered VALUES('2010551215','');我写的sql语句
SELECT student_name,COUNT(course_id)
FROM student s,registered r
WHERE s.student_id=r.student_id
GROUP BY student_name;
结果不对 求大虾指正
SELECT s.student_name, COUNT(course_id)
FROM student s, registered r
WHERE s.student_id = r.student_id(+)
GROUP BY s.student_name, s.student_id;
这样呢
所有全连接就查不出这条数据,所以要使用外连接。
以student表为主表,进行外连接就可以
SELECT s.student_name, COUNT(course_id)
FROM student s, registered r
WHERE s.student_id = r.student_id(+)
GROUP BY s.student_name