月度归档:2019年05月

mssql sqlserver 数据分组后取平均值的方法分享


摘要:
下文讲述分组数据取平均值的方法,如下所示:
实验环境:sql server 2008 R2


下文讲述,取一张学生成绩表中,
并为每个学生成绩后面加上”个人平均分”及”全班平均分”。

实现思路:
使用over开窗函数,对数据进行分组,并加上聚合计算函数。

create table [maomao365.com]
(keChengName nvarchar(30),
 chengJi     decimal(18,1),
 name        nvarchar(10)
 )
 go
 insert into [maomao365.com]
 (keChengName,chengJi,name)values
 ('语文',98,'张三'),
 ('数学',92,'张三'),
 ('语文',90,'李四'),
 ('数学',100,'李四'),
 ('语文',83,'王二'),
 ('数学',98,'王二')
 go
 
 --方式:使用开窗函数计算个人平均分及班级平均分
  
 
  select avg(chengJi)
   over(partition by name) as [个人平均分],
   avg(chengJi)
   over() as [班级平均分],
   * from [maomao365.com]   
   go
  
 truncate table [maomao365.com]
 drop table [maomao365.com]
 

mssql sqlserver sql脚本检测字符串是否为合法的邮箱地址的方法分享


摘要:
下文讲述使用sql脚本的方式验证字符串为合法邮箱地址的方法分享,如下所示:
实验环境:sql server 2008 R2



在进行合法邮箱的验证前,我们必须熟知一个正确邮箱地址具有哪些特性:
1.邮箱地址必须小于30位,并且3位
2.邮箱地址至少包含一个@
3.邮箱地址至少包含一个点
4.最后一个@后面必须包含一个点
5.邮箱地址不能以@或点结尾
6.邮箱地址必须以数字或英文或下划线开始

  
/*
定义一个邮箱验证的sql脚本
*/
    
IF  EXISTS (SELECT * FROM sys.objects
 WHERE object_id = OBJECT_ID(N'[dbo].[tmpMailCheck]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].tmpMailCheck
GO

 



create function [dbo].tmpMailCheck(@a varchar(8000) 
)
returns  nvarchar(100)  ---返回信息不为空,则代表异常邮箱地址
as
begin

/*
   1.邮箱地址必须小于30位,并且3位
   2.邮箱地址至少包含一个@
   3.邮箱地址至少包含一个点
   4.最后一个@后面必须包含一个点
   5.邮箱地址不能以@或点结尾
   6.邮箱地址必须以数字或英文或下划线开始
 */
declare @maomao365_com nvarchar(100)
declare @tmp varchar(20) ---临时变量 


set @maomao365_com =''

 
if len(@a) >30 
begin
  set @maomao365_com ='邮箱位数过长!'
  return @maomao365_com
end

if charindex('@',@a) <0 
begin
  set @maomao365_com ='邮箱不包含@'
  return @maomao365_com
end


if charindex('.',@a) <0 
begin
  set @maomao365_com ='邮箱不包含.'
  return @maomao365_com
end


set @tmp=substring(@a,len(@a),len(@a))


if @tmp ='@' or @tmp ='.'
begin
  set @maomao365_com ='邮箱不能以@或.结尾!'
  return @maomao365_com
end

set @Tmp = reverse(@a)
set  @tmp =  substring(@Tmp,0,charindex('@',@tmp))

if  charindex('.',@tmp) <=0
begin
   set @maomao365_com ='邮箱网址中不包含.字符!'
   return @maomao365_com
end


---首字母获取
set @tmp = substring(@a,1,1)
 
---判断是否包含字母 
if      PATINDEX('%[A-Za-z]%',@tmp) <= 0
  and   PATINDEX('%[0-9]%',@tmp)    <= 0
  and   @tmp !='_'  
begin
     set @maomao365_com ='邮箱前缀异常!'
     return @maomao365_com
end
 
 
 

    return  @maomao365_com
end
GO



---邮箱验证函数测试

select dbo.tmpMailCheck('@maomao365@maomao365.com'),
dbo.tmpMailCheck('*maomao365@maomao365.com'),
dbo.tmpMailCheck('maomao365@maomao365.com'),
dbo.tmpMailCheck('maomao365@maomao365.com')
go

IF  EXISTS (SELECT * FROM sys.objects
 WHERE object_id = OBJECT_ID(N'[dbo].[tmpMailCheck]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].tmpMailCheck
GO

mssql sqlserver varchar类型中存储数值类型如何按数值进行排序的方法分享


摘要:
由于种种原因,整型数据存储在varchar数据类型中,下文讲述”varchar数值内容”的排序方法
实验环境:sql server 2008 R2


实现思路:
对待排序的varchar纯数字字段加上一个int型形成一个新的字段,进行排序。


当表的varchar数据类型中,存储的为纯数字时,我们如何按照纯数字的方式进行排序,下文将通过举例的方式讲述如何排序

   create table [maomao365.com] 
    (keyId int identity(1,1),
     qty  varchar(30)
    )
    go
    insert into [maomao365.com] 
      (qty)values('10'),('12'),('18'),('2'),
      ('3'),('5'),('99')
      go
      ---varchar排序
      select * from [maomao365.com] 
        order by qty +0 asc 
   
   go
   truncate table [maomao365.com] 
   drop       table [maomao365.com]