sql常用语句

1 2 3 4

//排序 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以及时间格式化

1 2 3 4 5 6 7 8 9 10 11 12

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)

1 select * into #tabSubject from   --a.SubjectID,a.Question,a.CorrectAnswer,a.Explain,a.SubjectTypeID,a.CreateID,a.CreateDate,a.SubjectScore,a.ScoreSort
2     (--题目表(传入参数 HistPaperID,subjecttitleid)
3         select a.SubjectID,Question,Answer as CorrectAnswer,Explain,TypeID as SubjectTypeID,a.CreateID,CreateDate,Score as SubjectScore,Sort as ScoreSort from HistPaperSubject a
4         left join HistPaperSubjectScore b on a.subjectID=b.subjectID
5         where a.isdel=0 and a.HistPaperID=60 and b.HistPaperID=60 and b.subjecttitleid=193)    t
6 select * from #tabSubject
7 drop table #tabSubject

定义临时表并插入数据 

 1 --题目临时表
 2 select * into #tabSubject from   --a.SubjectID,a.Question,a.CorrectAnswer,a.Explain,a.SubjectTypeID,a.CreateID,a.CreateDate,a.SubjectScore,a.ScoreSort
 3     (--题目表(传入参数 HistPaperID,subjecttitleid)
 4         select a.SubjectID,Question,Answer as CorrectAnswer,Explain,TypeID as SubjectTypeID,a.CreateID,CreateDate,Score as SubjectScore,Sort as ScoreSort from HistPaperSubject a
 5         left join HistPaperSubjectScore b on a.subjectID=b.subjectID
 6         where a.isdel=0 and a.HistPaperID=60 and b.HistPaperID=60 and b.subjecttitleid=193)    t
 7 --学生答题临时表
 8 select * into #tabStudentAnswer from
 9     (select UserPaperID,UserID,SubmitDate,CreateDate,SubjectID,Answer as StudentAnswer,sort as SubjectSort,Score as StudentScore from UserPaper a
10     left join UserPaperSubject b on a.id=b.userpaperid
11     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
12 
13 create table #tabResult
14 (
15     SubjectID int,
16     Question nvarchar(MAX),
17     CorrectAnswer varchar(100),
18     Explain nvarchar(MAX),
19     SubjectTypeID int,
20     CreateID int,
21     CreateDate datetime,
22     SubjectScore decimal(3, 1),
23     ScoreSort int,
24     DeFenLv float,
25     [PerCent] varchar(20)
26 )    
27 declare @SubjectID int,--题目ID
28         @CorrectAnswer varchar(100),--正确答案
29         @CorrectNum int,--正确的题目数
30         @TotalNum int,--总的题目数
31         @DeFenLv float,--得分率(以浮点数形式表示)
32         @PerCent varchar(20);--得分率(以百分比形式表示)
33 while EXISTS(select SubjectID from #tabSubject)--循环题目临时表
34 begin
35     select @SubjectID=SubjectID,@CorrectAnswer=CorrectAnswer  from #tabSubject;
36     select @CorrectNum=Count(*) from #tabStudentAnswer where subjectid=@SubjectID and StudentAnswer=@CorrectAnswer--正确的题目数
37     select @TotalNum=Count(*) from #tabStudentAnswer where subjectid=@SubjectID--总的题目数
38     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)+'%'
39     --临时表（题目及其得分率组成）
40     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
44     delete from #tabSubject where subjectid=@SubjectID
45 end
46 drop table #tabSubject
47 drop table #tabStudentAnswer
48 drop table #tabResult
49 select * from #tabSubject
50 select * from #tabStudentAnswer where subjectid=500 and Studentanswer='B'
51 select * from #tabResult order by DeFenLv desc
52 select Count(1) as CorrectNum from #tabStudentAnswer where subjectid=500 and StudentAnswer='A'--正确的题目数
53 select Count(1) as TotalNum from #tabStudentAnswer where subjectid=500--总的题目数
54 
55 
56 
57 --整数相除得到浮点数 并转为百分比
58 declare @xiaoshudian float;
59 select @xiaoshudian=convert(float,23)/convert(float,49) 
60 select @xiaoshudian
61 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]