declare @t table(id int,d datetime,v float) insert into @t select 1,'2008-01-05',4000 insert into @t select 2,'2008-01-03',2000 insert into @t select 3,'2008-01-02',1000declare @利率 float set @利率=0.03 select a.*,a.v*datediff(d,b.d,a.d)*@利率 from @t a left join @t b on a.id=b.id-1 /* id d v ----------- ----------------------- ---------------------- ---------------------- 1 2008-01-05 00:00:00.000 4000 240 2 2008-01-03 00:00:00.000 2000 60 3 2008-01-02 00:00:00.000 1000 NULL */
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+1drop 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 行受影响)*/
select datadiff(dd,a.交易时间,b.交易时间) from tableName a left join tableName b on a.id = b.id+1
a和b是表的别名。非正规表达式,能对单表做复杂t-sql 我还么彻底领悟使用方法
create table tbf(id int,tim varchar(10),acc decimal(22,4)) 列了下相邻两条记录的时间,求个差就好了 select tim,(select min(tim) from tbf t where t.tim>tbf.tim) tim2 from tbf order by tbf
对的,这个只是别名 楼上的解法就可以了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+1drop 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 行受影响)*/
select datadiff(dd,a.交易时间,b.交易时间) from tableName a left join tableName b on a.id = b.id+1
对于你做的系统,数据量是很大的,不建议你用sql直接算 select * from 交易 where accountno='xxxx' order by 交易时间 然后在代码中算[交易时间]
[sql code]declare @利率 float set @利率=0.1 select 利息=datediff(dd,select 交易时间 from tableName where id=1,select 交易时间 from tableName where id=2)*@利率*4000 [/sql]
对的,这个只是别名 楼上的解法就可以了 SQL codecreate 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+1drop 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 行受影响)*/
首先,要求按指定顺序存在一个递增且无缺的id (如果没有,先按指定顺序插入到一个含id字段的表变量) 然后: declare @t table (fid,fd,fm) --id,日期,金额 select 4000*datediff(d,b.fd,a.fd)*a.fm 利息 from @t a left join @t b on a.fid=b.fid-1
a、b是别名,其实都是同一个@t
create table SalesItem ( salesid int identity, saledate datetime, balance money ) insert into SalesItem(saledate,balance) values('2009-1-1',899) insert into SalesItem(saledate,balance) values('2009-2-1',344) insert into SalesItem(saledate,balance) values('2009-2-21',333) insert into SalesItem(saledate,balance) values('2009-5-4',6665) select * from SalesItemselect datediff(day,s1.saledate,S2.saledate) as balancedate from SalesItem as S1 join SalesItem as S2 on S2.salesid = S1.salesid + 1
with XX as ( select *, Row_Number() over(Order By [Date Column] desc) as index from [Table] ) select datediff(t1.[Date Column] - t2.[Date Column]) * t1.[Money] * [日利率] as [结果] from XX t1 inner join XX t2 on t1.index = t2.index + 1 身边没有sql环境 直接手写 你自己测试一下(sql 2005以上版本)
create function (@dte_time datetime) returns float as return select 当前金额 from youtable where =(select min(交易时间) from youtable where =@dte_time)调用:select *,dbo.fun_diffval(交易时间)-当前金额 from youtable
create function (@dte_time datetime) returns float as return select 当前金额 from youtable where =(select min(交易时间) from youtable where >@dte_time) 调用: select *,dbo.fun_diffval(交易时间)-当前金额 from youtable
insert into @t select 1,'2008-01-05',4000
insert into @t select 2,'2008-01-03',2000
insert into @t select 3,'2008-01-02',1000declare @利率 float
set @利率=0.03
select a.*,a.v*datediff(d,b.d,a.d)*@利率 from @t a left join @t b on a.id=b.id-1
/*
id d v
----------- ----------------------- ---------------------- ----------------------
1 2008-01-05 00:00:00.000 4000 240
2 2008-01-03 00:00:00.000 2000 60
3 2008-01-02 00:00:00.000 1000 NULL
*/
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+1drop 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 行受影响)*/
列了下相邻两条记录的时间,求个差就好了
select tim,(select min(tim) from tbf t where t.tim>tbf.tim) tim2 from tbf order by tbf
对的,这个只是别名
楼上的解法就可以了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+1drop 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 行受影响)*/
我的理解是,如果有多个用户的交易情况,先将要查询的用户的交易情况查询出来按照交易时间排序放到一个临时表里,临时表设一个递增的ID,然后根据临时表的ID按照上边的方法查询
不会解就算了 千万别误导别人。
这种代码是大炸弹,出错了都不知道是怎么回事。
select * from 交易 where accountno='xxxx' order by 交易时间
然后在代码中算[交易时间]
set @利率=0.1
select 利息=datediff(dd,select 交易时间 from tableName where id=1,select 交易时间 from tableName where id=2)*@利率*4000
[/sql]
楼上的解法就可以了
SQL codecreate 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+1drop 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 行受影响)*/
(如果没有,先按指定顺序插入到一个含id字段的表变量)
然后:
declare @t table (fid,fd,fm) --id,日期,金额
select 4000*datediff(d,b.fd,a.fd)*a.fm 利息
from @t a
left join @t b on a.fid=b.fid-1
(
salesid int identity,
saledate datetime,
balance money
)
insert into SalesItem(saledate,balance)
values('2009-1-1',899)
insert into SalesItem(saledate,balance)
values('2009-2-1',344)
insert into SalesItem(saledate,balance)
values('2009-2-21',333)
insert into SalesItem(saledate,balance)
values('2009-5-4',6665)
select * from SalesItemselect datediff(day,s1.saledate,S2.saledate) as balancedate
from SalesItem as S1
join SalesItem as S2
on S2.salesid = S1.salesid + 1
with XX as
(
select *, Row_Number() over(Order By [Date Column] desc) as index from [Table]
)
select datediff(t1.[Date Column] - t2.[Date Column]) * t1.[Money] * [日利率] as [结果] from XX t1 inner join XX t2 on t1.index = t2.index + 1
身边没有sql环境 直接手写 你自己测试一下(sql 2005以上版本)
returns float
as
return select 当前金额 from youtable where =(select min(交易时间) from youtable where =@dte_time)调用:select *,dbo.fun_diffval(交易时间)-当前金额 from youtable
returns float
as
return select 当前金额 from youtable where =(select min(交易时间) from youtable where >@dte_time) 调用: select *,dbo.fun_diffval(交易时间)-当前金额 from youtable