我有一个非常简单的问题，但在网上很难找到.也许我正在搜索不正确的关键字，所以我想停下来问问你们，因为你们的网站对我的学习很有帮助.看下面的场景:

I have a simple enough issue that has been surprisingly difficult to locate online. Perhaps I am searching on improper keywords so I wanted to stop in and ask you guys because your site has been a blessing with my studies. See below scenario:

从 mystudies 中选择学生，count(*) 作为 Total，(未知变量:book1、book2、book3、book4 等...).

Select student, count(*) as Total, (the unknown variable: book1, book2, book3, book4, ect...) from mystudies.

基本上我想做的就是列出所有书籍，以获得与总计数相匹配的唯一学生 ID.有人可以为我指出正确的方向，读好书或其他任何东西，这样我就可以朝着正确的方向迈出一步吗?我假设它将通过左连接完成(不确定如何执行 x1、x2、x3 部分)，然后只需通过唯一的学生 ID 号(无重复)将两者链接起来，但在线上的每个人都指向枢轴但出现枢轴将所有行放入列而不是一列.SQL Server 2005 是首选平台.

Essentially all I would like to do is list out all books for a unique student id that matches the Total count. Could someone point me in the right direction, a good read or anything, so I can get a step going in the correct direction? I am assuming it would be done via a left join (not sure how to do the x1, x2, x3 part) and then just link the two by the unique student id number (no duplicates) but everyone online points to pivot but pivot appears to put all the rows into columns instead of one single column. SQL server 2005 is the platform of choice.

谢谢！

对不起

以下查询为表中所有重复条目生成我的唯一 ID(学生)和学生计数:

The following query produces my unique id (the student) and the student's count for all duplicate entries in the table:

select student, count(*) as Total 
from mystudies
group by student order by total desc

我不知道的部分是如何在表唯一 ID (boookid) 上创建左联接

the part I don't know is how to create the left join on the table unique id (boookid)

select mystudies1.student, mystudies1.total, mystudies2.bookid 
from  (  select student, count(*) as Total 
         from mystudies
         group by student
      ) mystudies1
      left join 
      (  select student, bookid 
         from mystudies
      ) mystudies2 
         on mystudies1.student=mystudies2.student
order by mystudies1.total desc, mystudies1.student asc

显然上面的行会产生类似于以下的结果:

Obviously the above row will produce results similar to the following:

Student    Total  BookID
000001    3        100001
000001    3        100002
000001    3        100003
000002    2        200001
000002    2        200002
000003    1        300001

但我真正想要的是类似于以下内容:

But what I actually want is something similar to the following:

Student    Total  BookID
000001     3      100001, 100002, 100003
000002     2      200001, 200002
000003     1      300001

我认为它必须在左连接中完成，这样它就不会改变对学生执行的实际计数.谢谢！

I assumed it had to be done in a left join so that it didn't alter the actual count being performed on the student. thanks!

推荐答案

在 SQL-Server 中使用 FOR XML Path 方法:

In SQL-Server use the FOR XML Path Method:

SELECT  Student,
        Total,
        STUFF(( SELECT  ', ' + BookID
                FROM    MyStudies books
                WHERE   Books.Student = MyStudies.Student
                FOR XML PATH(''), TYPE
                ).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS Books
FROM    (   SELECT  Student, COUNT(*) AS Total
            FROM    myStudies
            GROUP BY Student
        ) MyStudies

我之前已经完整解释了 XML PATH 方法的工作原理 这里.随着对我的回答的进一步改进，指出 这里

I have previously given a full explanation of how the XML PATH Method works here. With a further improvement to my answer pointed out here

SQL Server 小提琴

在 MySQL 和 SQLite 中，您可以使用 GROUP_CONCAT 函数:

In MySQL AND SQLite you can use the GROUP_CONCAT function:

SELECT  Student, 
        COUNT(*) AS Total, 
        GROUP_CONCAT(BookID) AS Books
FROM    myStudies
GROUP BY Student

MySQL 小提琴

SQLite 小提琴

在 Postgresql 中，您可以使用 ARRAY_AGG 函数:

In Postgresql you can use the ARRAY_AGG Function:

SELECT  Student, 
        COUNT(*) AS Total, 
        ARRAY_AGG(BookID) AS Books
FROM    myStudies
GROUP BY Student

Postgresql 小提琴

在 oracle 中，您可以使用 LISTAGG功能

In oracle you can use the LISTAGG Function

SELECT  Student, 
        COUNT(*) AS Total, 
        LISTAGG(BookID, ', ') WITHIN GROUP (ORDER BY BookID) AS Books
FROM    myStudies
GROUP BY Student

Oracle SQL Fiddle