![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
表概况:
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
大概是这种情况,我想求相邻两行的 交易时间 的差,注意是相邻两行,然后用40000.0×时间(2)与时间(1)的差×日利率=利息。
请高手帮帮忙吧,小弟很急的,最好能有代码
create table tbf(id int,tim varchar(10),acc decimal(22,4))
insert into tbf values(1,'2009/01/01',40000)
insert into tbf values(2,'2009/02/01',30000)
insert into tbf values(3,'2009/03/01',20000)
insert into tbf values(4,'2009/04/01',10000)
insert into tbf values(5,'2009/04/07',60000)
select datediff(dd,a.tim,b.tim),a.*
from tbf a
left join tbf b on a.id = b.id+1
drop table tbf
/*
id tim acc
----------- ----------- ---------- ---------------------------------------
NULL 1 2009/01/01 40000.0000
-31 2 2009/02/01 30000.0000
-28 3 2009/03/01 20000.0000
-31 4 2009/04/01 10000.0000
-6 5 2009/04/07 60000.0000
(5 行受影响)
*/
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
SQL实现split函数,自定义分割字符,自定义取出第几个分割字符前的字符串
自定义取出第几个分割字符前的字符串,默认位置(0)
格式:dbo.split(字段名,'分隔字符',取出的第几个字符串)
如果没有分隔的字符,则返回整个字符串。
如果取出的位置字符串的位置超出Index则返回空。
CREATE FUNCTION [dbo].[split]
(@str nvarchar(4000),@code varchar(10),@no int )
RETURNS varchar(200)
AS
BEGIN
declare @intLen int
declare @count int
declare @indexb int
declare @indexe int
set @intLen=len(@code)
set @count=0
set @indexb=1
if @no=0
if charindex(@code,@str,@indexb)<>0
return left(@str,charindex(@code,@str,@indexb)-1)
else
return @str
while charindex(@code,@str,@indexb)<>0
begin
set @count=@count+1
if @count=@no
break
set @indexb=@intLen+charindex(@code,@str,@indexb)
end
if @count=@no
begin
set @indexe=@intLen+charindex(@code,@str,@indexb)
if charindex(@code,@str,@indexe)<>0
return substring(@str,charindex(@code,@str,@indexb)+len(@code),charindex(@code,@str,@indexe)-charindex(@code,@str,@indexb)-len(@code))
else
return right(@str,len(@str)-charindex(@code,@str,@indexb)-len(@code)+1)
end
return ''
END
游标的使用
给出具体的例子:
declare @id nvarchar(20) --定义变量来保存ID号
declare @A float --定义变量来保存值
declare mycursor cursor for select * from tb_c --为所获得的数据集指定游标
open mycursor --打开游标
fetch next from mycursor into @id,@A --开始抓第一条数据
while(@@fetch_status=0) --如果数据集里一直有数据
begin
select tb_b.name,(tb_b.gz + @A) from tb_b where tb_b.id = @id --开始做想做的事(什么更新呀,删除呀)
fetch next from mycursor into @id,@A --跳到下一条数据
end
close mycursor --关闭游标
deallocate mycursor --删除游标
注:
//判断游标的状态
//0 fetch语句成功
//-1 fetch语句失败或此行不在结果集中
//-2被提取的行不存在
大数据的迁移用此个方法比较好。。
sqlbulkcopy
用来测试一个方法需要的时间
Stopwatch myWatch = new Stopwatch();