之前Django做了一个网站,其中关于课程有3张表:
- course_course,
- auth_user,
- course_course_student,
分表记录了课程信息、学生信息、学生参与课程的对应关系。
其中前2张表有对应的数据模型,而第3张表则是根据指定了ManyToManyKey自动生成:
9 class Course(models.Model):
10 ……
11 student = models.ManyToManyField(User, related_name='courses_joined', blank=True)
里面只有3个字段:
pragma table_info(course_course_student);
下面来练习使用SQL语句,实现这样几个查询:
查看每个course有哪些student | 统计每个user有多少个couse | 统计每个course有多少个student
1、查看每个course有哪些student
注意联合N张表,想要得到有意义的结果而不是泛泛的笛卡尔乘积,至少(N-1)个过滤条件、(跟图论某个结论很像?N个节点的图,至少N-1个线才能连通,多一个就不是最小连通)
2、不过这样罗列可不够,希望1个course只出现一次,后面跟一串student名字。
单独加group by title ,效果是:每个title只出现一次,username也只有第一个会出现……
要展示全部,用group_concat(username)。此处感谢 @SummerEnds 。
3、统计每个course有多少个student参与吧,名字其实是不重要的。
4、course和student是多对多的关系,反过来统计每个student参与了多少course:
用inner join可以,不过没有参加course的student不在此列:
想要统计到所有的student,包括参与course为0的,应该用外联:
注意这里select中不能用count(*),否则会得到错误的结果:
所以要指定count(courseid),course_id存在才count += 1。
暂时这样!