--排序
select Row_Number() over(order by a.UserName) as Num
--区分性别
case Sex when 0 then '男' else '女' end SexName
Sqlserver中tinyint, smallint, int, bigint的区别
bigint：从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据，存储大小为 8 个字节。一个字节就是8位，那么bigint就有64位
int：从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据，存储大小为 4 个字节。int类型，最大可以存储32位的数据
smallint：从-2^15(-32,768)到2^15-1(32,767)的整数数据，存储大小为 2 个字节。smallint就是有16位
tinyint：从0到255的整数数据，存储大小为 1 字节。tinyint就有8位。
case when以及时间格式化
select a.ApplyID,convert(nvarchar(20),a.TripTime,101) as TripTime,a.[Target],a.ApplyerNum,a.ApplyerName,
case a.CheckState when 0 then '待审核'
when 1 then '审核通过'
when 2 then '退回'
else '' end as CheckState,
case a.AssignState when 0 then '待分派'
when 1 then '已分派'
else '-' end as AssignState,
case a.DriverState when 0 then '退回'
when 1 then '已确认'
else '-' end as DriverState
from cmApply a
将选出来的结果集插入到临时表(该方式会自动创建临时表#tabSubject)
select * into #tabSubject from --a.SubjectID,a.Question,a.CorrectAnswer,a.Explain,a.SubjectTypeID,a.CreateID,a.CreateDate,a.SubjectScore,a.ScoreSort
(--题目表(传入参数 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) t
select * from #tabSubject
drop table #tabSubject
定义临时表并插入数据
--题目临时表
select * into #tabSubject from --a.SubjectID,a.Question,a.CorrectAnswer,a.Explain,a.SubjectTypeID,a.CreateID,a.CreateDate,a.SubjectScore,a.ScoreSort
(--题目表(传入参数 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) t
--学生答题临时表
select * into #tabStudentAnswer from
(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) t -- and b.subjectid=500 order by UserID,SubjectSort create table #tabResult
(
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 #tabSubject)--循环题目临时表
begin
select @SubjectID=SubjectID,@CorrectAnswer=CorrectAnswer from #tabSubject;
select @CorrectNum=Count(*) from #tabStudentAnswer where subjectid=@SubjectID and StudentAnswer=@CorrectAnswer--正确的题目数
select @TotalNum=Count(*) from #tabStudentAnswer 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 #tabResult select a.SubjectID,a.Question,a.CorrectAnswer,a.Explain,a.SubjectTypeID,a.CreateID,a.CreateDate,a.SubjectScore,a.ScoreSort,@DeFenLv,@PerCent from #tabSubject a where a.subjectid=@SubjectID
delete from #tabSubject where subjectid=@SubjectID
end
drop table #tabSubject
drop table #tabStudentAnswer
drop table #tabResult
select * from #tabSubject
select * from #tabStudentAnswer where subjectid=500 and Studentanswer='B'
select * from #tabResult order by DeFenLv desc
select Count(1) as CorrectNum from #tabStudentAnswer where subjectid=500 and StudentAnswer='A'--正确的题目数
select Count(1) as TotalNum from #tabStudentAnswer where subjectid=500--总的题目数 --整数相除得到浮点数 并转为百分比
declare @xiaoshudian float;
select @xiaoshudian=convert(float,23)/convert(float,49)
select @xiaoshudian
select convert(float,25)/convert(float,41) as DeFenLv,cast(cast(round(convert(float,25)/convert(float,41)*100,0) as decimal(18,0)) as varchar)+'%' as [PerCent]