代码：
declare @Subject table(--题目表变量 SubjectID int, Question nvarchar(MAX), CorrectAnswer varchar(100), Explain nvarchar(MAX), SubjectTypeID int, CreateID int, CreateDate datetime, SubjectScore decimal(3, 1), ScoreSort int)insert into @Subject(SubjectID,Question,CorrectAnswer,Explain,SubjectTypeID,CreateID,CreateDate,SubjectScore,ScoreSort)select a.SubjectID,a.Question,a.CorrectAnswer,a.Explain,a.SubjectTypeID,a.CreateID,a.CreateDate,a.SubjectScore,a.ScoreSort from (--题目表(传入参数 HistPaperID,subjecttitleid) select a.SubjectID,Question,Answer as CorrectAnswer,Explain,TypeID as SubjectTypeID,a.CreateID,CreateDate,Score as SubjectScore,Sort as ScoreSort from HistPaperSubject a left join HistPaperSubjectScore b on a.subjectID=b.subjectID where a.isdel=0 and a.HistPaperID=60 and b.HistPaperID=60 and b.subjecttitleid=193) a--select * from @Subject declare @StudentAnswer table(--学生答题表变量 UserPaperID int, UserID int, SubmitDate datetime, CreateDate datetime, SubjectID int, StudentAnswer varchar(100), SubjectSort int, StudentScore decimal(3, 1))insert into @StudentAnswer(UserPaperID,UserID,SubmitDate,CreateDate,SubjectID,StudentAnswer,SubjectSort,StudentScore)select b.UserPaperID,b.UserID,b.SubmitDate,b.CreateDate,b.SubjectID,b.StudentAnswer,b.SubjectSort,b.StudentScore from (--题目表(传入参数 HistPaperID) select UserPaperID,UserID,SubmitDate,CreateDate,SubjectID,Answer as StudentAnswer,sort as SubjectSort,Score as StudentScore from UserPaper a left join UserPaperSubject b on a.id=b.userpaperid where a.HistPaperID=60 and b.HistPaperID=60 and a.[status]=1 and a.isdel=0) b--select * from @StudentAnswerdeclare @Result table(--最终结果报表 SubjectID int, Question nvarchar(MAX), CorrectAnswer varchar(100), Explain nvarchar(MAX), SubjectTypeID int, CreateID int, CreateDate datetime, SubjectScore decimal(3, 1), ScoreSort int, DeFenLv float, [PerCent] varchar(20))declare @SubjectID int,--题目ID @CorrectAnswer varchar(100),--正确答案 @CorrectNum int,--正确的题目数 @TotalNum int,--总的题目数 @DeFenLv float,--得分率(以浮点数形式表示) @PerCent varchar(20);--得分率(以百分比形式表示)while EXISTS(select SubjectID from @Subject)--循环题目表变量begin select @SubjectID=SubjectID,@CorrectAnswer=CorrectAnswer from @Subject; select @CorrectNum=Count(*) from @StudentAnswer where subjectid=@SubjectID and StudentAnswer=@CorrectAnswer--正确的题目数 select @TotalNum=Count(*) from @StudentAnswer where subjectid=@SubjectID--总的题目数 select @DeFenLv=convert(float,@CorrectNum)/convert(float,@TotalNum),@PerCent=cast(cast(round(convert(float,@CorrectNum)/convert(float,@TotalNum)*100,0) as decimal(18,0)) as varchar)+'%' --最终表（题目及其得分率组成） insert into @Result select a.SubjectID,a.Question,a.CorrectAnswer,a.Explain,a.SubjectTypeID,a.CreateID,a.CreateDate,a.SubjectScore,a.ScoreSort,@DeFenLv,@PerCent from @Subject a where a.subjectid=@SubjectID delete from @Subject where subjectid=@SubjectIDendselect * from @Result
原标题：sql 将查询的结果集一次性插入到表变量中
关键词：sql
我知道了 