面试题：多对多的关系

初学者进，大牛飘过

假设有一个学生选课的业务模型

请画出可能涉及到的数据库表的属性

User

ID NAME SEX
1 张三 1
2 lisi         1
3 王五 0
4 赵六 1

Course

ID NAME
1 java
2 php
3 c++
4 c
5 p
6 a

Score(关系表)

ID UID CID
1 1 1
2 1 2
3 2 3
4 2 2
5 3 3
6 4 2
7 1 3

A:根据学员ID,查询该学员所选的课程信息

这个简单，飘过

B：课程按所选学员人数由高到低排名，列出排名1-2的课程信息

一定一定记住：下面是oracle数据库（rownum关键字）

SELECT
t.*
FROM
(
SELECT
rownum rn,
s.*
FROM
(
SELECT
c. NAME,
count(c. NAME)
FROM
USER u
JOIN Score s ON u.ID = s.UID
JOIN Course c ON s.CID = c.ID
GROUP BY
c. NAME
ORDER BY
count(c. NAME) DESC
) s
WHERE
rownum <= 2
) t
WHERE
rn >= 1

下面是mysql数据库（limit关键字）

举例说明limit:

1.取前5条数据

select * from t limit 0,5 或select * from t limt 5

2.取第11条数据到第15条数据

select * from t limit 10,5

以上是例子

SELECT
c. NAME,
count(c. NAME)
FROM
USER u
JOIN Score s ON u.ID = s.UID
JOIN Course c ON s.CID = c.ID
GROUP BY
c. NAME
ORDER BY
count(c. NAME) DESC
LIMIT 0,2
 C:列出所有选择2门课以上的学生信息，由高到低排序

SELECT
u. NAME,
count(c. NAME)
FROM
USER u
JOIN Score s ON u.ID = s.UID
JOIN Course c ON s.CID = c.ID
GROUP BY
u. NAME
HAVING
count(c. NAME) >= 2