问一个数据库问题有3个表，一个放学生名字，一个放学科名字，一个放学生学的课。
student(sname)
course(cname)
study(sname,cname)
数据是这样的
student: {(A), (B)}
course: {(CS1101), (CS1102)}
study: {(A, CS1101), (B, CS1102)}

现在我想知道每个学生学了几门课(自我感觉这是个很普通的目的，一点也不刁钻)，我希望得到的答案是{(A, 2), (B, 0)}
于是我这样
select student.sname, count(study.cname) from student, study where student.sname=study.sname group by student.sname
但得出的结果是{(A, 2)}。没有B，因为B没有学任何课。
有没有什么办法达到这个目的?不能有嵌入查询，最好不要创建临时表。


下面是我在mysql上的实验结果。
mysql> create table student (sname char(10) primary key);
Query OK, 0 rows affected (0.08 sec)

mysql> create table course (cname char(10) primary key);
Query OK, 0 rows affected (0.05 sec)

mysql> create table study (sname char(10), cname char(10));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into student values ('A'), ('B');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into course values ('CS1101'), ('CS1102');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into study (sname, cname) values ('A', 'CS1101'), ('A', 'CS1102');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select student.sname, count(study.cname) from student, study where student.sname=study.sname group by student.sname;
+-------+--------------------+
| sname | count(study.cname) |
+-------+--------------------+
| A | 2 |
+-------+--------------------+
1 row in set (0.00 sec)
[吴永铮 (1-8 0:38, Long long ago)] [ 传统版 | sForum ][登录后回复]1楼

answermysql> select student.sname, count(study.cname) from student left join study on (student.sname=study.sname) group by student.sname;
+-------+--------------------+
| sname | count(study.cname) |
+-------+--------------------+
| A | 2 |
| B | 0 |
+-------+--------------------+
2 rows in set (0.00 sec)
[快快跑 (1-8 8:24, Long long ago)] [ 传统版 | sForum ][登录后回复]2楼

(引用 快快跑:answermysql> select student.sname, count(study.cname) from student left join study on (student.sname=study.sname) group by stude...)多谢多谢[吴永铮 (1-8 18:50, Long long ago)] [ 传统版 | sForum ][登录后回复]3楼