021 数据库学习笔记--求百分比 + null值赋空 + 数字小数处理 + 字符串截取

--通过函数处理除法，获取百分比
if object_id('GetPassRate','fn') is not null drop function GetPassRate
go
create function [dbo].[GetPassRate]( @num1 int, @num2 int)
returns nvarchar(10)
as
begin
 declare @res varchar(10)
 select @res = case when @num1 is null or @num2 is null then null
                    --when @num1 = 0 then '0%'
					when @num1 = 0 then '100%'
                    when @num2 = 0 then null
                else concat(cast(cast(@num1  * 1.0 /@num2 as decimal(18,4)) * 100  as decimal(18,2)),'%')   end
  return @res
end

select [dbo].GetPassRate(1,2) as passrate
select [dbo].GetPassRate(18,20) as passrate
select [dbo].GetPassRate(0,1) as passrate

select round(convert(float,1)/convert(float,2),2)
--sql server 除法 1/2=0.5
--0.5
select round(convert(float,1)/convert(float,2),2)
--50%  0%
select str(round(convert(float,1)/convert(float,2),2)*100) +'%' as passrate
select str(round(convert(float,0)/convert(float,2),0)*100) +'%' as passrate

示例二：

if object_id('GetPassRate2','fn') is not null drop function GetPassRate2
go
create function [dbo].[GetPassRate2]( @num1 int, @num2 int)
returns nvarchar(10)
as
begin
 declare @res varchar(10)
 select @res = case when @num1 is null or @num2 is null then null
                    --when @num1 = 0 then '0%'
					when @num1 = 0 then '100%'
                    when @num2 = 0 then null
                --else concat(cast(cast(@num1  * 1.0 /@num2 as decimal(18,2)) * 100  as decimal(18,2)),'%')   end
				else concat(cast(cast(@num1  * 1.0 /@num2 as float) * 100  as float),'%')   end
  return @res
end
go
select [dbo].GetPassRate2(1,2) as passrate,[dbo].GetPassRate2(0,1) as passrate,
[dbo].GetPassRate2(18,20) as passrate,[dbo].GetPassRate2(0,0) as passrate
go

注：调用函数，需[dbo]. 函数名

--保留2位小数的关键是字段类型要转成浮点数字段类型
select CONVERT(DECIMAL(10,2),(100.0*1/7)) 
--在2个字段前面乘以100.0，注意是100.0不是100
select str(CONVERT(DECIMAL(10,2),(100.0*1/2))) +'%' as passrate
select str(CONVERT(DECIMAL(10,2),(100.0*0/2))) +'%' as passrate

--把被除字段转成浮点数字段类型
--取巧方法：
select CONVERT(DECIMAL(10,2),(100.0*1/(2*1.0)))  --这种方法比较取巧，在被除字段乘以1.0
select CONVERT(DECIMAL(10,2),(100.0*0/(2*1.0)))

--正规的方法：
select CONVERT(DECIMAL(10,2),(100*1/CONVERT(FLOAT,2)))  
select CONVERT(DECIMAL(10,2),(100*1/CONVERT(FLOAT,4)))  
select CAST(CONVERT(DECIMAL(10,2),(100*0/CONVERT(FLOAT,2))) AS varchar) + '%'
select CAST(CONVERT(DECIMAL(10,2),(100*1/CONVERT(FLOAT,2))) AS varchar) + '%'
select CAST(CONVERT(DECIMAL(10,2),(100*1/CONVERT(FLOAT,4))) AS varchar) + '%'

NULL值赋空

select  t.CHECKDOCTOR,  t.* from TableSeries t where  t.CHECKDOCTOR is null
select  case when t.CHECKDOCTOR is null then '' else t.CHECKDOCTOR end  as Doctor ,  t.* from TableSeries t where  t.CHECKDOCTOR is null

字符串处理

 --从1截取到3
 SELECT substring('abcd', 1, 3) as 字符串截取
 --从左截取5位
 select left('HelloWorld',5) as 左侧截取
 --从右截取5位
 select right('HelloWorld',5) as 右侧截取
 --从指定的位置开始截取指定长度
 select substring('HelloWorld',charindex('W','HelloWorld'),3) as 指定位置指定长度截取

 --获取指定字符串出现的索引位置
 select charindex('orld','HelloWorld001')

--包含
declare @str varchar(50)
set @str='HelloWorld001'
 select  
 case 
	when charindex('orld',@str)>0 then '包含' 
	when charindex('orld','002001')>0  then '不包含'
 end as 包含
 go
--不包含
declare @str varchar(50)
set @str='002001'
 select  
 case 
	when charindex('orld',@str)>0 then '包含' 
	when charindex('orld','HelloWorld001')>0  then '不包含'
 end as 不包含

数字小数点处理

精度是数字中的数字个数。小数位数是数中小数点右边的数字个数。例如，数 123.45 的精度是 5，小数位数是 2。

----------------------------------数据类型----------------------------------
--最大10位（包括小数点后面），保留小数点后2位，四舍五入
--decimal(param1,param2)
--第一个参数表示最大长度，包括小数点后面位数； 第二个参表示小数点后位数。
select Convert(decimal(10,2),123.456),Convert(decimal(6,3),123.4562) 

--concat，字符串拼接
select concat(Convert(decimal(18,2),2*1.0/4)*100,'%')
select concat(Convert(decimal(18,2),2*1.0/3)*100,'%')

--round()函数
--round()函数保留N位有效数字，四舍五入，有效数字后面变为0
--Round() 函数，如 Round(@num,2) 参数 @num是要原数据，2 表示保留两位有效数字
--@num的数是整数时，会忽略小数部分（其实就是进行了四舍五入）
--针对Round() ,还有第三个参数， 第三个参数1是不进行四舍五入，0是进行
select round(9.0/4,2,1) 		
select round(24.1255,2),round(24.1255,3),round(24.1255,4)
select ceiling(24.222*1000)/1000

--在使用方便程度上Convert()函数要优先于Round() 函数；
--除了以上两种，还可以直接对原数进行转换，用CAST()
--保留小数点后两位： 注：9表示正数--返回值则为正数；9.0是浮点型--返回值也为浮点型
select CAST( 9/4 as decimal(18,2)) --运行后结果：2.00
select CAST( 9*1.0/4 as decimal(18,2))--运行后结果：2.25

select convert(decimal(18,2),1.0*1/2) as mydecimal
select convert(float,1.0*1/8) as myfloat,convert(float,1.0*1/3)as myfloat,cast(convert(float,1.0*1/3)as decimal(18,2)) as myfloat