查询结果集直接更新到同一张表中的某个字段

       在使用数据库查询时候，通常需要将查询得到的结果集更新回到原来的表中，除了写函数的方法外，可以使用通用表达式CTE(common table) 来解决.实例如下:先需要对如下表按照score 排名，然后将排名写会排名字段中。

CREATE TABLE table1 (    Id INT,    Title VARCHAR(2),    score  float,   排名  int);WITH CTE AS(SELECT id, RANK() OVER(ORDER BY score) AS 排名 FROM table1) --with 前一个语句必须有分号，为了保险可以直接在其前面加一个分号UPDATE t2  SET 总名次 = 排名 FROM CTE t1 INNER JOIN table1  t2 ON t1.id = t2.id

毕设代码：

USE [GD_SMAS]GO/****** Object:  StoredProcedure [dbo].[Exam_Calc]    Script Date: 2015/1/7 15:00:49 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[Exam_Calc]@eid UNIQUEIDENTIFIER,@jiedu BIT,@fudu BIT,@onlySort BIT,@result NVARCHAR(255)   OUTPUTASBEGINDECLARE @tableName VARCHAR(50)SELECT @tableName=tableName FROM dbo.tExams WHERE id= @eid    IF @onlySort = 0  --先计算成绩，然后排名BEGINDECLARE @test VARCHAR(1000)      DECLARE @vCourseStr VARCHAR(1000)      --汇总信息字符串SELECT @vCourseStr=vCourses FROM dbo.tExams WHERE id=@eidIF LEN(LTRIM(RTRIM(@vCourseStr)))>0--存在汇总科目(再次验证)BEGINDECLARE @vIndex INT --逗号下标DECLARE @oldVindex INT   --前一个逗号下标DECLARE @dhIndex INT  --等号下标，其前面的为科目，后面为会总项DECLARE @courseName VARCHAR(20) --科目名称DECLARE @struct VARCHAR(100)  --成绩构成项DECLARE @updateSql VARCHAR(1000)DECLARE @zongfen VARCHAR(1000) = ''SET @oldVindex = 0SET @vIndex =  CHARINDEX(',',@vCourseStr);  --获取第一个逗号下标，如果没有逗号则直接跳过while循环    --SELECT @vIndex AS '第一个逗号下标'DECLARE @oneCourseStr VARCHAR(1000) --一门课程的汇总信息WHILE @vIndex>0 --但是此时最后一个获取的@vIndex=0需要在while后面再处理BEGINSET @oneCourseStr =RIGHT(LEFT(@vCourseStr,@vIndex-1),@vIndex-@oldVindex-1)SET @dhIndex = CHARINDEX('=', @oneCourseStr)SET @courseName = LEFT(@oneCourseStr,@dhIndex-1)SET @struct = RIGHT(@oneCourseStr,LEN(@oneCourseStr)-@dhIndex)SET @zongfen =@courseName+'+'+ @zongfenSET @updateSql = 'UPDATE Exam.dbo.'+ @tableName+'  SET   '+ @courseName+'='+ @struct+'  WHERE id IN (SELECT id FROM Exam.dbo.'+ @tableName+')'EXEC(@updateSql) SET @oldVindex = @vIndexSET @vIndex =  CHARINDEX(',',@vCourseStr,@vIndex+1);  --获取下一个逗号下标                           END--while退出后处理最后一个，如果是只有一个科目，则while循环体不执行，直接执行下面代码DECLARE @lastVindex INT = @oldVindexSET @oneCourseStr = RIGHT(@vCourseStr,LEN(@vCourseStr)-@lastVindex)  --获取最后一门课程SET @dhIndex = CHARINDEX('=',@oneCourseStr)SET @courseName = LEFT(@oneCourseStr,@dhIndex-1)SET @zongfen =  @zongfen+@courseNameSET @struct = RIGHT(@oneCourseStr,LEN(@oneCourseStr)-@dhIndex)SET @updateSql = 'UPDATE Exam.dbo.'+ @tableName+'  SET   '+ @courseName+'='+ @struct+'  WHERE id IN (SELECT id FROM Exam.dbo.'+ @tableName+')'EXEC(@updateSql)--最后计算总分SET @updateSql = 'UPDATE Exam.dbo.'+ @tableName+'  SET  总分='+ @zongfen+'  WHERE id IN (SELECT id FROM Exam.dbo.'+ @tableName+')'EXEC(@updateSql)ENDEND --如果需要计算成绩，则先执行上面的代码然后执行下面的进行排序，否则，直接执行下面排序代码DECLARE @condition VARCHAR(100) ='  WHERE 生源类型=''应届'''IF @jiedu = 0 AND @fudu =  1BEGINSET @condition='  WHERE 生源类型=''应届'''+' OR 生源类型=''复读'''ENDELSE IF @jiedu = 1 AND @fudu = 0     SET @condition='  WHERE 生源类型=''应届'''+' OR 生源类型=''借读'''ELSE IF @jiedu =1 AND @fudu = 1SET @condition=''ELSESET @condition = @condition   DECLARE @rankSql VARCHAR(1000)SET @rankSql ='SELECT RANK() OVER(ORDER BY 总分) AS 排名, 总分,*  FROM Exam.dbo.test '+@condition;SET @rankSql =';WITH CTE AS(SELECT id, RANK() OVER(ORDER BY 总分) AS 排名 FROM Exam.dbo.test)   UPDATE t2  SET 总名次 = 排名 FROM CTE t1 INNER JOIN Exam.dbo.'+ @tableName+' t2 ON t1.id = t2.id'EXEC(@rankSql)SET  @result = 'succeed' END