SQL Server使用PARTITION BY分区示例

windowsliusheng

于 2019-12-10 12:00:10 发布

阅读量443

点赞数

分类专栏： MSSQL

本文链接：https://blog.csdn.net/windowsliusheng/article/details/103473149

版权

MSSQL 专栏收录该内容

6 篇文章

订阅专栏

--修改科目名到学生特长字段，分数大于140分，多个科目大于140分则取其中一个
SELECT ROW_NUMBER() OVER(PARTITION BY t.StudentID,t.StudentName ORDER BY t.CourseName) as GroupNum,
t.StudentID,t.StudentName,t.CourseName into #StudentGood from
(SELECT DISTINCT b.StudentID,b.StudentName,c.CourseName FROM StudentScore as a left join Student as b on a.StudentID=b.StudentID
 left join Course as c on a.CourseID=c.CourseID WHERE a.Score>140) as t;
 update Student set Good=b.CourseName from #StudentGood as b where Student.StudentID=b.StudentID and b.GroupNum=1
 drop table #StudentGood