SQL选择:具有可变列数的二维选择 [英] SQL select: two-dimensional select with variable column count

问题描述

CREATE TABLE activities(activityid, name);
CREATE TABLE activity_scores(activityid, studentid, score);   
CREATE TABLE students (id, name);

是否可以用这种格式编写单个SELECT查询来为每个学生产生一个结果?

Is there any way to write a single SELECT query that will produce one result for each student, in this format?

studentid | studentname | activity1_score | activity2_score | activity3_score [...]

通过多个查询来做到这一点很简单:

It's trivial to do it with multiple queries:

for each studentid in "select id from students":
    print studentid
    for each activity in "select activityid from activities":
        select score from activity_scores where studentid = studentid
        print score

(伪代码，我知道这是不正确的)

(pseudocode, I know it's not accurate)

肯定有一种方法可以通过单个SELECT查询创建此结果，对吧?

Surely there's a way to create this result with a single SELECT query, right?

推荐答案

MySQL，SQLite以及其他RDBMS都具有名为GROUP_CONCAT，
的东西 哪个应该做您想要的事情(未经测试-不知道您在activity_scores表上的加入条件):

MySQL, SQLite, and perhaps other RDBMSs have something called GROUP_CONCAT,
which should do something like what you want (not tested -- don't know your join condition on the activity_scores table):

SELECT   students.studentid
         , students.studentname
         , GROUP_CONCAT(activity_scores.score)

FROM     activity_scores 
         INNER JOIN activities 
         ON activities.activityid = activity_scores.activityid
         INNER JOIN students 
         ON students.studentid = activities.studentid

GROUP BY students.studentid 
         , students.studentname

但比我更聪明的人可能会说，做这样的事情会混淆两个截然不同的步骤，这两个步骤在分开放置时最容易处理:

but smarter folks than me might say that doing something like this confuses two distinct steps which are easiest to deal with when left separate:

• 数据访问/收集
• 数据展示

这篇关于SQL选择:具有可变列数的二维选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！