如何选择这样的2张桌子

本文介绍了如何选择这样的2张桌子的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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!

希望有帮助.