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;
结果不对 求大虾指正。。。
6 个解决方案
#1
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;
这样呢
#2
结果不对的原因是因为你用的是全连接，而如果有的学生没有注册的话，在registered表中是没有数据的，
所有全连接就查不出这条数据，所以要使用外连接。
以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
#3
空值能插入到PRIMARY KEY里?
#4
当然不能
#5
括号里的+什么意思？
#6
还是不行
#1
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;
这样呢
#2
结果不对的原因是因为你用的是全连接，而如果有的学生没有注册的话，在registered表中是没有数据的，
所有全连接就查不出这条数据，所以要使用外连接。
以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
#3
空值能插入到PRIMARY KEY里?
#4
当然不能
#5
括号里的+什么意思？
#6
还是不行