摘要:
下文讲述sqlserver中算数溢出的处理方法分享,如下所示:
实验环境:sql server 2008 R2
convert转换方法1
CONVERT(bigint,列名)
cast转换方法1
cast(列名 as decimal(18,2))
摘要:
下文讲述sqlserver中算数溢出的处理方法分享,如下所示:
实验环境:sql server 2008 R2
convert转换方法1
CONVERT(bigint,列名)
cast(列名 as decimal(18,2))
摘要:
下文讲述使用sql脚本查询 销售业务表 每日销售数据相对前一天的数据增长情况,如下所示:
实验环境:sql server 2008 R2
create table [maomao365.com_SaleData] ( saleDate date, saleQty int ) go ---生成基础数据 insert into [maomao365.com_SaleData] (saleDate,saleQty)values('2019-10-1',2), ('2019-10-3',8),('2019-10-5',9), ('2019-10-8',7) go declare @mind date,@maxd date select @mind = min(saleDate) from [maomao365.com_SaleData] select @maxd = max(saleDate) from [maomao365.com_SaleData] ---生成连续 此范围内连续天数 SELECT DATEADD(DAY, number, CONVERT(DATETIME, @mind)) d into #t1 FROM master..spt_values WHERE type = 'P' AND number BETWEEN 0 AND DATEDIFF(DAY, @mind,@maxd)-1; ---对连续天数内的销售增长情况分析 with cteName (keyIdNew,d,qty) as ( select ROW_NUMBER() over(order by d desc) as keyIdNew,a.d,isnull(b.saleQty,0) as saleQty from #t1 a left join [maomao365.com_SaleData] b on a.d = b.saleDate ) select a.*, isnull(b.qty,0)-isnull(a.qty,0) as [增长数] from cteName a left join cteName b on b.keyIdNew = (a.keyIdNew+1) truncate table #t1 drop table #t1 go truncate table [maomao365.com_SaleData] drop table [maomao365.com_SaleData]
相关阅读:
mssql sqlserver使用系统表spt_values生成连续日期的方法分享
mssql sqlserver 使用sql脚本取上一条数据与下一条数据之差,递归计算的方法分享
摘要:
下文讲述在sqlserver数据库中使用sql脚本获取存储过程内容的三种方法分享,如下所示:
实验环境:sql server 2008 R2
例:
定义一个存储过程pr_test
create proc pr_test @a varchar(10) as begin print '存储过程内容' end go
例:获取存储过程pr_test的内容
获取存储过程内容方法1:
declare @proc_info nvarchar(max) SELECT @proc_info= text FROM syscomments WHERE id = ( SELECT id FROM sysobjects WHERE name = 'pr_test') print @proc_info
获取存储过程内容方法2:
declare @proc_info nvarchar(max) SELECT @proc_info= definition FROM sys.sql_modules JOIN sys.objects ON sys.sql_modules.object_id=sys.objects.object_id and type='P' and sys.objects.name='pr_test' print @proc_info
获取存储过程内容方法3:
exec sp_helptext 'pr_test'
获取存储过程内容方法4:
select object_definition(object_id('pr_test'))