| « | December 2025 | » | | 日 | 一 | 二 | 三 | 四 | 五 | 六 | | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | | | | |
|
公告 |
|
 工作中的Janet．加油！
 生活中的Janet.i-life
|
| Blog信息 |
|
blog名称:
日志总数:30
评论数量:10
留言数量:0
访问次数:86302
建立时间:2005年8月31日 |
| |
|
 [Java Tips]JOIN table
文章收藏
janetvsfei 发表于 2006/1/5 12:59:05 |
|
Tip time:(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_idWHERE names.codename <> ''GROUP BY names.codenameORDER 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. |
|
|