如何选择这样的2张桌子 [英] how to select 2 table like this

问题描述

如何选择具有这种条件的表.

How to select table with condition like this.

我有一张桌子，上面放着各种各样的主题.

I have a table that holds all kinds of subjects like this.

table subjects

|===========|============|
|Subjects id|  Subjects  |
|===========|============|
|     01    |mathematics |      
|     02    |biology     |     
|     03    |geography   |       
|     04    |physics     |      
|===========|============|

然后我还有一个表格来保存每个这样的学生的价值.

then I also have a table to hold the value of each student like this.

table score

|==========|============|===============|
|Student id|Subjects id |     Score     |
|==========|============|===============|
|  10001   |     01     |       8       |
|  10001   |     02     |       6       |
|  10001   |     03     |       7       |
|  10001   |     04     |       9       |
|  10002   |     01     |       5       |
|  10002   |     02     |       7       |
|  10002   |     03     |       10      |
|  10002   |     04     |       7       |
|  10003   |     01     |       6       |
|  10003   |     02     |       7       |
|  10003   |     03     |       8       |
|  10003   |     04     |       9       |
|==========|============|===============|

我想用下表形式创建查询，但我不知道该怎么做.

I want to create a query with a form like the following table but i dont know how to make it.

|==========|=============|=========|===========|=========|
|Student id| mathematics | biology | geography | physics |
|==========|=============|=========|===========|=========|
|  10001   |     8       |    6    |     7     |    9    |
|  10002   |     5       |    7    |    10     |    7    |
|  10003   |     6       |    7    |     8     |    9    |
|==========|=============|=========|===========|=========|

请帮助我解决这个问题.对不起，我的英语不好.我仍然是初学者

please help me to solve this problem. Sorry my english is bad. I am still beginner

推荐答案

您有一些方法可以做到这一点，但是如果不创建临时表，可以执行以下操作:

You have some ways to do that, but trying not to create temporary tables, you can do something like that:

select
    s.id,
    avg(case when sb.id = '01' then s.score end) as math,
    avg(case when sb.id = '02' then s.score end) as bio

from student s
join subject sb on (sb.id = s.subject_id)

group by s.id

只需根据需要将总和/格行填入其他主题！

Just fill the sum/case lines to the other subjects as you need!

希望有帮助.

这篇关于如何选择这样的2张桌子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！