玻璃小屋 阅读(12834) 评论(7)
SELECT *
FROM (((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity) INNER JOIN Wedlock ON Member.Wedlock=Wedlock.Wedlock
ORDER BY MemberDate DESC

评论列表
玻璃小屋
re: INNER JOIN 连接多个表的用法~~~
老大,要不仔细讲讲用法?? ^O^
竹叶
re: INNER JOIN 连接多个表的用法~~~
刚才乱了 重来
(Background: This database is used to keep track of scores for students in my classes.)

So in this case, I have three tables, one has student's "codename"
(as posting their real name on the web is a no-no) and an index
(there is more data in this table, but this is all you really need to know.)
Then there's a table with the assignments, containing the assignment name,
and an index for each assignment. Finally, there is a scores table,
which has for each paper I get turned in, a student_id (releated to the student index)
an act_id (related to the assignments index) and a score.

It looked something like this:
students table:


+----+---------------+
| id | codename |
+----+---------------+
| 1 | Budy |
+----+---------------+

assignments table:
+--------+------------+
| act_id | name |
+--------+------------+
| 1 | Activity 1 |
| 2 | Activity 2 |
+--------+------------+

scores table:
+------------+--------+-------+
| student_id | act_id | score |
+------------+--------+-------+
| 1 | 1 | 10 |
| 1 | 2 | 10 |
+------------+--------+-------+

Now the problem was, I wanted to have the assignments listed across the top,
and the scores next to the names. Something like this:
+---------------+------------+------------+-------+
| codename | Activity 1 | Activity 2 | Total |
+---------------+------------+------------+-------+
| budy | 10 | 10 | 20 |
+---------------+------------+------------+-------+

So here's how the sql statement ended up:
SELECT names.codename,
s1.score AS "Score1", s1.comment AS "Comments1",
s2.score AS "Score2", s2.comment AS "Comments2",
SUM(st.score) AS "Total"
FROM students names
LEFT JOIN scores s1 ON s1.act_id=1 AND names.id=s1.student_id
LEFT JOIN scores s2 ON s2.act_id=2 AND names.id=s2.student_id
LEFT JOIN scores st ON names.id=st.student_id
WHERE names.codename <> ''
GROUP BY names.codename
ORDER BY names.codename;

As you can see, for each activity, I need to add another left join,
but it looks exactly like the last one, thus it is easy to build through a program like php.
I hope this helps someone out.
manager
20
manager
20

发表评论
切换编辑模式