你的位置:首页 > 数据库

[数据库]sql 将查询的结果集一次性插入到表变量中


代码:

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