如何在SQL Server中每个组的最后一条记录后插入新行

e3bfsja2  于 2022-11-21  发布在  SQL Server
关注(0)|答案(1)|浏览(268)

我尝试在SQL Server中的每个组后插入新行。
这是原始表

这是预期输出

我在这里遇到的问题是,我不知道如何在我创建的新视图中将其中两个混合在一起。
要获取最后一条记录:

WITH new_students_table AS 
(
    SELECT 
        m.*, 
        ROW_NUMBER() OVER (PARTITION BY Subject ORDER BY Subject DESC) AS student
    FROM 
        students AS m
)
SELECT * 
FROM ranked_messages 
WHERE student = 1;

要获得总和:

SELECT 
    [Subject] = COALESCE([Subject], 'Total'), 
    [Score] = SUM([Score])
FROM 
    students
GROUP BY 
    GROUPING SETS(([Subject]), ());

任何帮助都将不胜感激。

wrrgggsh

wrrgggsh1#

这看起来很管用。
请注意row_number()Grouping Sets的第一个

Select Subject   = case when StudentID is null Then 'Total '+Subject else Subject end
      ,StudentID 
      ,Score=sum(Score)
 From  (Select *
              ,RN=row_number() over (partition by Subject order by StudentID)
         From  YourTable 
       ) src
 GROUP BY GROUPING SETS( ([Subject],[StudentID],[RN])
                        ,([Subject])
                        );

结果

相关问题