在使用数据库查询时候,通常需要将查询得到的结果集更新回到原来的表中,除了写函数的方法外,可以使用通用表达式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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Exam_Calc]
@eid UNIQUEIDENTIFIER,
@jiedu BIT,
@fudu BIT,
@onlySort BIT,
@result NVARCHAR(255) OUTPUT
AS
BEGIN
DECLARE @tableName VARCHAR(50)
SELECT @tableName=tableName FROM dbo.tExams WHERE id= @eid
IF @onlySort = 0 --先计算成绩,然后排名
BEGIN
DECLARE @test VARCHAR(1000)
DECLARE @vCourseStr VARCHAR(1000) --汇总信息字符串
SELECT @vCourseStr=vCourses FROM dbo.tExams WHERE id=@eid
IF LEN(LTRIM(RTRIM(@vCourseStr)))>0 --存在汇总科目(再次验证)
BEGIN
DECLARE @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 = 0
SET @vIndex = CHARINDEX(',',@vCourseStr); --获取第一个逗号下标,如果没有逗号则直接跳过while循环
--SELECT @vIndex AS '第一个逗号下标'
DECLARE @oneCourseStr VARCHAR(1000) --一门课程的汇总信息
WHILE @vIndex>0 --但是此时最后一个获取的@vIndex=0需要在while后面再处理
BEGIN
SET @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+'+'+ @zongfen
SET @updateSql = 'UPDATE Exam.dbo.'+ @tableName+' SET '+ @courseName+'='+ @struct+' WHERE id IN (SELECT id FROM Exam.dbo.'+ @tableName+')'
EXEC(@updateSql)
SET @oldVindex = @vIndex
SET @vIndex = CHARINDEX(',',@vCourseStr,@vIndex+1); --获取下一个逗号下标
END
--while退出后处理最后一个,如果是只有一个科目,则while循环体不执行,直接执行下面代码
DECLARE @lastVindex INT = @oldVindex
SET @oneCourseStr = RIGHT(@vCourseStr,LEN(@vCourseStr)-@lastVindex) --获取最后一门课程
SET @dhIndex = CHARINDEX('=',@oneCourseStr)
SET @courseName = LEFT(@oneCourseStr,@dhIndex-1)
SET @zongfen = @zongfen+@courseName
SET @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)
END
END
--如果需要计算成绩,则先执行上面的代码然后执行下面的进行排序,否则,直接执行下面排序代码
DECLARE @condition VARCHAR(100) =' WHERE 生源类型=''应届'''
IF @jiedu = 0 AND @fudu = 1
BEGIN
SET @condition=' WHERE 生源类型=''应届'''+' OR 生源类型=''复读'''
END
ELSE IF @jiedu = 1 AND @fudu = 0
SET @condition=' WHERE 生源类型=''应届'''+' OR 生源类型=''借读'''
ELSE IF @jiedu =1 AND @fudu = 1
SET @condition=''
ELSE
SET @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