初学者进,大牛飘过
假设有一个学生选课的业务模型
请画出可能涉及到的数据库表的属性
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关键字)
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
举例说明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