sql-case when,row_number

 

--排序
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]