如何通过列- SQL服务器进行分组

I need to group by an UniqueIdentifier column, the table also contains the XML column.

我需要通过一个uniqufier列进行分组，该表也包含XML列。

Table schema: StudentMark:

表模式:StudentMark:

CREATE TABLE [dbo].[StudentMark]
(
    [StudentMarkId] [int] IDENTITY(1,1) NOT NULL,
    [StudentId] [uniqueidentifier] NULL,
    [SubjectId] [uniqueidentifier] NULL,
    [ScoreInfo] [xml] NULL,
    [GeneratedOn] [datetime2](2) NOT NULL,

    CONSTRAINT [PK_StudentMark] 
       PRIMARY KEY CLUSTERED ([StudentMarkId] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Sample seed data

种子样本数据

INSERT INTO [dbo].[StudentMark] ([StudentId], [SubjectId], [ScoreInfo], GeneratedOn])
VALUES ('FC3CB475-B480-4129-9190-6DE880E2D581', '0D72F79E-FB48-4D3E-9906-B78A9D105081', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-10 10:20:15'),
       ('0F4EF48C-93E3-41AA-8295-F6B0E8D8C3A2', '0D72F79E-FB48-4D3E-9906-B78A9D105081', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-10 10:20:15'),
       ('0F4EF48C-93E3-41AA-8295-F6B0E8D8C3A2', 'AB172272-D2E9-49E1-8040-6117BB6743DB', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-16 09:06:20'),
       ('FC3CB475-B480-4129-9190-6DE880E2D581', 'AB172272-D2E9-49E1-8040-6117BB6743DB', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-16 09:06:20');

Requirement: I need to group by [dbo].[StudentMark].[StudentId] and take the latest record.

要求:我需要按[dbo].[StudentMark]分组。[研究人员]并取得最新的记录。

I tried the following SQL query but it is causing an error

我尝试了下面的SQL查询，但它导致了一个错误

SELECT 
    MAX([StudentMarkId]), [StudentId], [SubjectId], [ScoreInfo], [GeneratedOn]
FROM 
    [dbo].[StudentMark] 
GROUP BY 
    [StudentId]

Error:

错误:

Column 'dbo.StudentMark.SubjectId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

“dbo.StudentMark列。主观性在选择列表中是无效的，因为它不包含在聚合函数或GROUP BY子句中。

I refereed the following question but I can't fix it: Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

我引用了以下问题，但我无法修复它:在select列表中，列的原因无效，因为它不包含在聚合函数或GROUP BY子句中

Kindly assist me.

请帮助我。

3 个解决方案

#1

Use ROW_NUMBER to calculate position within group:

使用ROW_NUMBER计算组内位置:

SELECT *
FROM (
    SELECT *,
      ROW_NUMBER() OVER(PARTITION BY StudentId ORDER BY StudentMarkId DESC) AS rn
    FROM [dbo].[StudentMark]) sub
WHERE sub.rn = 1;

#2

An alternative solution works best if you have a Students table:

如果你有一个学生表格，另一种解决方案效果最好:

select sm.*
from students s cross apply
     (select top 1 sm.*
      from studentmark sm
      where sm.studentid = s.studentid
      order by sm.generatedon desc
     ) sm;

#3

You cannot group by XML or TEXT columns, you would first need to convert to varchar(max):

不能按XML或文本列分组，首先需要转换为varchar(max):

SELECT 
    MAX([StudentMarkId]), [StudentId], [SubjectId],
    CONVERT(XML, CONVERT(VARCHAR(MAX), [ScoreInfo])) DetailXML,
    [GeneratedOn]
FROM 
    [dbo].[StudentMark] 
GROUP BY 
    [StudentId], [SubjectId], 
    CONVERT(VARCHAR(MAX), [ScoreInfo]), [GeneratedOn]

On the first line, it is converted to varchar(max) to match the GROUP BY clause, and later it is re-cast back to XML.

在第一行，它被转换为varchar(max)以匹配GROUP BY子句，然后它被重铸回XML。

#1