create table FangKuan
(
Fid int identity primary key not null,
FBianHao varchar(12) not null,
FName varchar(10) not null,
FJinE int not null,
FStartTime datetime not null,/*放款开始时间*/
FEndTime datetime not null,/*放款结束时间*/
FLiXi float not null
)create table Fu_xi
(
Fid int identity primary key not null,/*流水号*/
FJinE float not null,/*付息金额*/
FKId int not null,/*对应的放款ID号*/
) 想用一条sql语句或存储过程查询出这样的结果:
编号 姓名 已付利息 未付利息
1 abc 300 700select
(select sum(fjine) from fu_xi),
datediff(month,b.fstarttime,b.fendtime)*b.flixi*b.fjine/100
from
fangkuan b ,fu_xi a
where
a.fkid=b.fid上面的语句可以求出已付和未付,如果再加上fname和fbianhao就会出错,而且还想按fname和fbianhao分组
应该怎么写
(
Fid int identity primary key not null,
FBianHao varchar(12) not null,
FName varchar(10) not null,
FJinE int not null,
FStartTime datetime not null,/*放款开始时间*/
FEndTime datetime not null,/*放款结束时间*/
FLiXi float not null
)create table Fu_xi
(
Fid int identity primary key not null,/*流水号*/
FJinE float not null,/*付息金额*/
FKId int not null,/*对应的放款ID号*/
) 想用一条sql语句或存储过程查询出这样的结果:
编号 姓名 已付利息 未付利息
1 abc 300 700select
(select sum(fjine) from fu_xi),
datediff(month,b.fstarttime,b.fendtime)*b.flixi*b.fjine/100
from
fangkuan b ,fu_xi a
where
a.fkid=b.fid上面的语句可以求出已付和未付,如果再加上fname和fbianhao就会出错,而且还想按fname和fbianhao分组
应该怎么写
fangkuan表
Fid FBianHao FName FJinE FStartTime FEndTime FLiXi
1 20071023 abc 10000 2007-10-23 2008-10-23 1fu_xi表Fid FJinE FKId
1 300 1
2 300 2
10000 1
10000*1%=100
编号 姓名 已付利息 未付利息
1 abc 300 700 --------------------
不对吧?
Fid FBianHao FName FJinE FStartTime FEndTime FLiXi
1 20071023 abc 10000 2007-10-23 2008-10-23 1 fu_xi表 Fid FJinE FKId
1 300 1
2 300 1 未付利息=datediff(month,b.fstarttime,b.fendtime)*b.flixi*b.fjine/100-(select sum(fjine) from fu_xi)
insert @fangkuan select 1, '20071023', 'abc', 10000, '2007-10-23', '2008-10-23', 1 declare @fu_xi table(Fid int,FJinE int, FKId int)
insert @fu_xi select 1, 300 , 1
union all select 2 , 300 , 1
select
a.FId,
a.FName,
[已付利息]=isnull((select sum(FJinE) from @fu_xi where FKId=a.FId),0),
[未付利息]=(datediff(m,a.FStartTime,a.FEndTime)*a.FLiXi*a.FJinE/100)-isnull((select sum(FJinE) from @fu_xi where FKId=a.FId),0)
from
@fangkuan a--select
a.FId,
a.FName,
[已付利息]=isnull(b.FJinE,0),
[未付利息]=(datediff(m,a.FStartTime,a.FEndTime)*a.FLiXi*a.FJinE/100)-isnull(b.FJinE,0)
from
@fangkuan a
left join
(select sum(FJinE) as FJinE,FKId from @fu_xi group by FKId)b
on a.Fid=b.FKId
(所影响的行数为 1 行)
(所影响的行数为 2 行)FId FName 已付利息 未付利息
----------- ---------- ----------- -----------------------------------------------------
1 abc 600 600.0(所影响的行数为 1 行)FId FName 已付利息 未付利息
----------- ---------- ----------- -----------------------------------------------------
1 abc 600 600.0(所影响的行数为 1 行)