SQL select：具有可变列数的二维选择

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

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

有没有办法编写一个SELECT查询,以这种格式为每个学生产生一个结果?

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)

(伪代码,我知道它不准确)

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

当然有一种方法可以使用单个SELECT查询创建此结果,对吧?

6 个解决方案

#1

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):

MySQL,SQLite和其他RDBMS都有一个名为GROUP_CONCAT的东西,它应该做你想要的东西(没有测试 - 不知道你在activity_scores表上的连接条件):

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:

但是比我聪明的人可能会说做这样的事情会混淆两个不同的步骤,这些步骤在分开时最容易处理:

data access / gathering

数据访问/收集

data presentation

#2

You're basically looking for a pivot table. If you just want to use pure ANSI SQL, then there's no way to do this. SQL only generates result sets with a predictable number of columns (not counting select *'s).

你基本上在寻找一个数据透视表。如果您只想使用纯ANSI SQL,则无法执行此操作。 SQL只生成具有可预测列数的结果集(不计算select *')。

However, there may be a technology-specific way to do it. Which database engine are you using? SQL Server 2005 has the ability to do a pivot table.

但是,可能有一种技术特定的方法来实现它。您使用的是哪个数据库引擎? SQL Server 2005具有执行数据透视表的功能。

#3

This result set shape offends relational algebra (on which SQL is based). See SQL antipattern post #2 What are the most common SQL anti-patterns?

此结果集形状冒犯了关系代数(SQL所基于的)。请参阅SQL反模式帖子#2最常见的SQL反模式是什么?

You should issue this query and format the results on the client:

您应该发出此查询并在客户端上格式化结果:

SELECT s.name, a.name, x.score
FROM Activity_Score as x
  JOIN Students s
  ON x.StudentID = s.StudentID
  JOIN Activity a
  ON x.ActivityID = a.ActivityID
ORDER BY s.name, a.name, x.score

#4

One of the most frequently asked questions on SO.

有关SO的最常见问题之一。

The reason this is not supported in ANSI SQL is that the result set is not well-defined - it will have an arbitrarily varying number of columns.

ANSI SQL不支持这一原因的原因是结果集没有明确定义 - 它将具有任意数量的列。

However, knowing the number of columns, one can generate code to do it, the example solution I always give generates code for SQL Server 2005 using the PIVOT operator (the column list is static, which is why this needs to be done with dynamic SQL) using dynamic SQL and then executes it.

但是,知道列数,可以生成代码来执行此操作,我总是使用PIVOT运算符为SQL Server 2005生成代码的示例解决方案(列列表是静态的,这就是为什么需要使用动态SQL完成的原因) )使用动态SQL然后执行它。

#5

If the activities are known and can be specified in the query, then its relatively easy, if a little verbose, assuming your SQL dialect supports it. For example:

如果活动是已知的并且可以在查询中指定,那么相对容易,如果有点冗长,假设您的SQL方言支持它。例如:

SELECT s.id, s.name,
  (SELECT score FROM activity_scores as JOIN activities a ON a.activityid = as.activityid WHERE studentid = s.id AND a.activityname = 'Basketball') basketball_score,
  (SELECT score FROM activity_scores as JOIN activities a ON a.activityid = as.activityid WHERE studentid = s.id AND a.activityname = 'Football') football_score,
  ...

Basically this is called a crosstab query. If you want to do it dynamically then its harder and you'll probably need to resort to either code or a stored procedure so will depend on your database. Here is an example using SQL Server.

基本上这称为交叉表查询。如果你想动态地做它然后更难,你可能需要求助于代码或存储过程,因此将取决于你的数据库。以下是使用SQL Server的示例。

#6

In SQL Server 2005/2008 you can try returning the activities/scores for each student as an xml set. Not ideal but works. Something like:

在SQL Server 2005/2008中,您可以尝试将每个学生的活动/分数作为xml集返回。不理想,但有效。就像是:

SELECT s.name, 
(select a.name, x.score FROM FROM Activity_Score as x  
JOIN Activity a  ON x.ActivityID = a.ActivityID 
WHERE x.StudentID = s.StudentID FOR XML AUTO) Activities
FROM Students s
ORDER BY s.name

#1