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 @StudentAnswer

declare @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=@SubjectID

end

select * from @Result

--停止死锁的进程：

declare @spid  int

Set @spid  = 57 --锁表进程

declare @sql varchar(1000)

set @sql='kill '+cast(@spid  as varchar)

exec(@sql)

select * from sys.sysprocesses where hostname='SALE-PC'--查看SQL进程的详细信息