这个SQL语句应该怎么写,在线等待,急!!!谢谢!
表 A (主表)注:贷款主表
SysID：贷款号 Date：贷款日期 Bank:贷款银行 Sum：实际贷款金额
SysID Date Bank Sum
DK07001 2007-04-01 中国银行 1000
DK07002 2007-04-12 农业银行 2000
DK07003 2007-04-15 中国银行 3000
DK07004 2007-04-22 中国银行 4000
DK07005 2007-04-26 农业银行 5000
表 B (从表)注:还款从表
SysID:主从表关联字段 Date：还款日期 Sum:每次还款金额
SysID Date Sum
DK07002 2007-04-10 200
DK07002 2007-04-20 400
DK07004 2007-04-26 100
DK07004 2007-05-26 500
联接后产生视图 C
SysID Date Bank Sum Date1 Sum1
DK07001 2007-04-01 中国银行 1000 NULL NULL
DK07002 2007-04-12 农业银行 2000 2007-04-10 200 ??
DK07002 2007-04-12 农业银行 2000 2007-04-20 400 ??
DK07003 2007-04-15 中国银行 3000 NULL NULL
DK07004 2007-04-22 中国银行 4000 2007-04-26 100 ??
DK07004 2007-04-22 中国银行 4000 2007-05-26 500 ??
DK07005 2007-04-26 农业银行 5000 NULL NULL
我在进行分类汇总时需要还款日期，所以关联两表，但当我关联以后就会产生多余的贷款金额，请高手指点！我期望的结是:
SysID Date Bank Sum Date1 Sum1
DK07001 2007-04-01 中国银行 1000 NULL NULL
DK07002 2007-04-12 农业银行 2000 2007-04-10 200 ??
DK07002 2007-04-12 农业银行 NULL 2007-04-20 400 ??
DK07003 2007-04-15 中国银行 3000 NULL NULL
DK07004 2007-04-22 中国银行 4000 2007-04-26 100 ??
DK07004 2007-04-22 中国银行 NLL 2007-05-26 500 ??
DK07005 2007-04-26 农业银行 5000 NULL NULL
请问这种语句该怎么写，有什么好的办法，我急，谢谢！
[解决办法]
慢慢看,up
[解决办法]
頂..........,學習
[解决办法]
我来看看
[解决办法]
if object_id( 'pubs..贷款主表 ') is not null
drop table 贷款主表
go
create table 贷款主表(SysID varchar(10),Date varchar(10),Bank varchar(10),[Sum] int)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07001 ', '2007-04-01 ', '中国银行 ', 1000)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07002 ', '2007-04-12 ', '农业银行 ', 2000)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07003 ', '2007-04-15 ', '中国银行 ', 3000)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07004 ', '2007-04-22 ', '中国银行 ', 4000)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07005 ', '2007-04-26 ', '农业银行 ', 5000)
go
if object_id( 'pubs..还款从表 ') is not null
drop table 还款从表
go
create table 还款从表(SysID varchar(10),Date varchar(10),[Sum] int)
insert into 还款从表(SysID,Date,[Sum]) values( 'DK07002 ', '2007-04-10 ', 200)
insert into 还款从表(SysID,Date,[Sum]) values( 'DK07002 ', '2007-04-20 ', 400)
insert into 还款从表(SysID,Date,[Sum]) values( 'DK07004 ', '2007-04-26 ', 100)
insert into 还款从表(SysID,Date,[Sum]) values( 'DK07004 ', '2007-05-26 ', 500)
go
select a.* , b.date date1 , b.[sum] [sum1] from 贷款主表 a
left join 还款从表 b
on a.SysID = b.SysID
drop table 贷款主表,还款从表
/*
SysID Date Bank Sum date1 sum1
---------- ---------- ---------- ----------- ---------- -----------
DK07001 2007-04-01 中国银行 1000 NULL NULL
DK07002 2007-04-12 农业银行 2000 2007-04-10 200
DK07002 2007-04-12 农业银行 2000 2007-04-20 400
DK07003 2007-04-15 中国银行 3000 NULL NULL
DK07004 2007-04-22 中国银行 4000 2007-04-26 100
DK07004 2007-04-22 中国银行 4000 2007-05-26 500
DK07005 2007-04-26 农业银行 5000 NULL NULL
（所影响的行数为 7 行）
*/
[解决办法]
这样的表结构直接搞出来怕是不可能
[解决办法]
select SysID,Date,Bank,
(select
case count(1)
when 1 then Sum
else null
end
from a
where SysID=a.SysID
)
, b.[sum] [sum1] from 贷款主表 a
left join 还款从表 b
on a.SysID = b.SysID
[解决办法]
没必要用视图吧.用连接就行了.
left join
inner join
right join
------解决方案--------------------
create table 贷款表(贷款号 varchar(20),贷款日期 datetime,贷款银行 varchar(20),贷款金额 int)
create table 还款表(贷款号 varchar(20),还款日期 datetime,每次还款金额 int)
insert 贷款表 select 'DK07001 ', '2007-04-01 ', '中国银行 ',1000
union all select 'DK07002 ', '2007-04-12 ', '农业银行 ',2000
union all select 'DK07003 ', '2007-04-15 ', '中国银行 ',3000
union all select 'DK07004 ', '2007-04-22 ', '中国银行 ',4000
union all select 'DK07005 ', '2007-04-26 ', '农业银行 ',5000
insert 还款表 select 'DK07002 ', '2007-04-10 ',200
union all select 'DK07002 ', '2007-04-20 ',400
union all select 'DK07004 ', '2007-04-26 ',100
union all select 'DK07004 ', '2007-04-26 ',500
select id=identity(int,1,1),* into # from
(
select a.贷款号,贷款日期,贷款银行,贷款金额,还款日期,每次还款金额 from 贷款表 a left join 还款表 b
on a.贷款号=b.贷款号
)c
select 贷款号,贷款日期,贷款银行,
贷款金额=case when not exists(select 1 from # where 贷款号=a.贷款号 and 贷款日期=a.贷款日期 and 贷款银行=a.贷款银行
and id <a.id) then 贷款金额 else null end
,还款日期,每次还款金额 from # a
drop table #,贷款表,还款表
[解决办法]
if object_id( 'pubs..贷款主表 ') is not null
drop table 贷款主表
go
create table 贷款主表(SysID varchar(10),Date varchar(10),Bank varchar(10),[Sum] int)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07001 ', '2007-04-01 ', '中国银行 ', 1000)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07002 ', '2007-04-12 ', '农业银行 ', 2000)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07003 ', '2007-04-15 ', '中国银行 ', 3000)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07004 ', '2007-04-22 ', '中国银行 ', 4000)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07005 ', '2007-04-26 ', '农业银行 ', 5000)
go
if object_id( 'pubs..还款从表 ') is not null
drop table 还款从表
go
create table 还款从表(SysID varchar(10),Date varchar(10),[Sum] int)
insert into 还款从表(SysID,Date,[Sum]) values( 'DK07002 ', '2007-04-10 ', 200)
insert into 还款从表(SysID,Date,[Sum]) values( 'DK07002 ', '2007-04-20 ', 400)
insert into 还款从表(SysID,Date,[Sum]) values( 'DK07004 ', '2007-04-26 ', 100)
insert into 还款从表(SysID,Date,[Sum]) values( 'DK07004 ', '2007-05-26 ', 500)
go
create view 视图C
as
select a.* , b.date date1 , b.[sum] [sum1] from 贷款主表 a
left join 还款从表 b
on a.SysID = b.SysID
GO
select SysID,Date,Bank,
case when exists(select * from 视图c where SysID=a.SysID and date1 <a.date1)
then null else sum end sum,
Date1,sum1
from 视图C a
====================================
SysID Date Bank Sum Date1 Sum1
DK070012007-04-01中国银行1000NULLNULL
DK070022007-04-12农业银行20002007-04-10200
DK070022007-04-12农业银行NULL2007-04-20400
DK070032007-04-15中国银行3000NULLNULL
DK070042007-04-22中国银行40002007-04-26100
DK070042007-04-22中国银行NULL2007-05-26500
DK070052007-04-26农业银行5000NULLNULL
[解决办法]
DK070012007-04-01 00:00:00.000中国银行 1000NULLNULL
DK070022007-04-12 00:00:00.000农业银行 20002007-04-10 00:00:00.000200
DK070022007-04-12 00:00:00.000农业银行 NULL2007-04-20 00:00:00.000400
DK070032007-04-15 00:00:00.000中国银行 3000NULLNULL
DK070042007-04-22 00:00:00.000中国银行 40002007-04-26 00:00:00.000100
DK070042007-04-22 00:00:00.000中国银行 NULL2007-04-26 00:00:00.000500
DK070052007-04-26 00:00:00.000农业银行 5000NULLNULL
[解决办法]
create table t
(SysID varchar(15), Date varchar(10), Bank varchar(20), Sum smallint, Date1 varchar(10), Sum1 smallint)
insert into t
select 'DK07001 ', '2007-04-01 ', '中国银行 ', 1000, null, NULL union all
select 'DK07002 ', '2007-04-12 ', '农业银行 ', 2000, '2007-04-10 ', 200 union all
select 'DK07002 ', '2007-04-12 ', '农业银行 ', NULL, '2007-04-20 ', 400 union all
select 'DK07003 ', '2007-04-15 ', '中国银行 ', 3000, null, NULL union all
select 'DK07004 ', '2007-04-22 ', '中国银行 ', 4000, '2007-04-26 ', 100 union all
select 'DK07004 ', '2007-04-22 ', '中国银行 ', null , '2007-05-26 ', 500 union all
select 'DK07005 ', '2007-04-26 ', '农业银行 ', 5000, null, NULL
select b.SysID,a.Date as 借款日期 ,a.Date1 as 还款日期 ,b.sum as 借款金额 ,b.sum1 as 还款金额 ,isnull(b.sum-b.sum1,0)as 剩佘金额 from t a
left join
(
select SysID,sum(isnull(sum,0))as sum,sum(isnull(sum1,0))as sum1
from t
group by SysID
)b
on a.SysID=b.SysID
SysID 借款日期 还款日期 借款金额 还款金额 剩佘金额
--------------- ---------- ---------- ----------- ----------- -----------
DK07001 2007-04-01 NULL 1000 0 1000
DK07002 2007-04-12 2007-04-10 2000 600 1400
DK07002 2007-04-12 2007-04-20 2000 600 1400
DK07003 2007-04-15 NULL 3000 0 3000
DK07004 2007-04-22 2007-04-26 4000 600 3400
DK07004 2007-04-22 2007-05-26 4000 600 3400
DK07005 2007-04-26 NULL 5000 0 5000
（所影响的行数为 7 行）
不知道有没有明白LZ的意思
[解决办法]
up
[解决办法]
declare @贷款主表 table(SysID varchar(100),Date datetime,Bank varchar(100),Sum int)
insert into @贷款主表 select 'DK07001 ', '2007-04-01 ', '中国银行 ', 1000
insert into @贷款主表 select 'DK07002 ', '2007-04-12 ', '农业银行 ', 2000
insert into @贷款主表 select 'DK07003 ', '2007-04-15 ', '中国银行 ', 3000
insert into @贷款主表 select 'DK07004 ', '2007-04-22 ', '中国银行 ', 4000
insert into @贷款主表 select 'DK07005 ', '2007-04-26 ', '农业银行 ', 5000
declare @还款从表 table(SysID varchar(100),Date datetime,Sum int)
insert into @还款从表 select 'DK07002 ', '2007-04-10 ', 200
insert into @还款从表 select 'DK07002 ', '2007-04-20 ', 400
insert into @还款从表 select 'DK07004 ', '2007-04-26 ', 100
insert into @还款从表 select 'DK07004 ', '2007-05-26 ', 500
select
SysID,Date,Bank,
Sum=(case when Sum1 = (select top 1 Sum1 from
(
select a.SysID,a.Date,a.Bank,a.Sum,Date1=b.Date,Sum1=b.Sum
from @贷款主表 a left join @还款从表 b on a.SysID=b.SysID
) y
where Sum = x.Sum) then Sum else null end),
Date1,Sum1
from
(
select
a.SysID,a.Date,a.Bank,a.Sum,Date1=b.Date,Sum1=b.Sum
from
@贷款主表 a
left join
@还款从表 b
on
a.SysID=b.SysID
) x
/*
SysIDDate Bank SumDate1 Sum1
-------------------------------------------------------
DK070012007-04-01 中国银行 NULLNULL NULL
DK070022007-04-12 农业银行 20002007-04-10 200
DK070022007-04-12 农业银行 NULL2007-04-20 400
DK070032007-04-15 中国银行 NULLNULL NULL
DK070042007-04-22 中国银行 40002007-04-26 100
DK070042007-04-22 中国银行 NULL2007-05-26 500
DK070052007-04-26 农业银行 NULLNULL NULL
*/
[解决办法]
--更正
declare @贷款主表 table(SysID varchar(100),Date datetime,Bank varchar(100),Sum int)
insert into @贷款主表 select 'DK07001 ', '2007-04-01 ', '中国银行 ', 1000
insert into @贷款主表 select 'DK07002 ', '2007-04-12 ', '农业银行 ', 2000
insert into @贷款主表 select 'DK07003 ', '2007-04-15 ', '中国银行 ', 3000
insert into @贷款主表 select 'DK07004 ', '2007-04-22 ', '中国银行 ', 4000
insert into @贷款主表 select 'DK07005 ', '2007-04-26 ', '农业银行 ', 5000
declare @还款从表 table(SysID varchar(100),Date datetime,Sum int)
insert into @还款从表 select 'DK07002 ', '2007-04-10 ', 200
insert into @还款从表 select 'DK07002 ', '2007-04-20 ', 400
insert into @还款从表 select 'DK07004 ', '2007-04-26 ', 100
insert into @还款从表 select 'DK07004 ', '2007-05-26 ', 500
select
SysID,Date,Bank,
Sum=(case when Sum1 = (select top 1 Sum1 from
(
select
a.SysID,a.Date,a.Bank,
Sum=isnull(a.Sum,0),Date1=b.Date,Sum1=isnull(b.Sum,0)
from @贷款主表 a left join @还款从表 b on a.SysID=b.SysID
) y
where Sum = x.Sum order by Sum1) then Sum else 0 end),
Date1,Sum1
from
(
select
a.SysID,a.Date,a.Bank,Sum=isnull(a.Sum,0),Date1=b.Date,Sum1=isnull(b.Sum,0)
from
@贷款主表 a
left join
@还款从表 b
on
a.SysID=b.SysID
) x
/*
SysIDDate Bank SumDate1 Sum1
-----------------------------------------------
DK070012007-04-01 中国银行 1000NULL 0
DK070022007-04-12 农业银行 20002007-04-10 200
DK070022007-04-12 农业银行 02007-04-20 400
DK070032007-04-15 中国银行 3000NULL 0
DK070042007-04-22 中国银行 40002007-04-26 100
DK070042007-04-22 中国银行 02007-05-26 500
DK070052007-04-26 农业银行 5000NULL 0
*/
[解决办法]
if object_id( 'pubs..贷款主表 ') is not null
drop table 贷款主表
go
create table 贷款主表(SysID varchar(10),Date varchar(10),Bank varchar(10),[Sum] int)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07001 ', '2007-04-01 ', '中国银行 ', 1000)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07002 ', '2007-04-12 ', '农业银行 ', 2000)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07003 ', '2007-04-15 ', '中国银行 ', 3000)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07004 ', '2007-04-22 ', '中国银行 ', 4000)
insert into 贷款主表(SysID,Date,Bank,[Sum]) values( 'DK07005 ', '2007-04-26 ', '农业银行 ', 5000)
go
if object_id( 'pubs..还款从表 ') is not null
drop table 还款从表
go
create table 还款从表(SysID varchar(10),Date varchar(10),[Sum] int)
insert into 还款从表(SysID,Date,[Sum]) values( 'DK07002 ', '2007-04-10 ', 200)
insert into 还款从表(SysID,Date,[Sum]) values( 'DK07002 ', '2007-04-20 ', 400)
insert into 还款从表(SysID,Date,[Sum]) values( 'DK07004 ', '2007-04-26 ', 100)
insert into 还款从表(SysID,Date,[Sum]) values( 'DK07004 ', '2007-05-26 ', 500)
go
select case when H3.Flag= '1 ' then ' ' else D.SysID end as SysID,
case when H3.Flag= '1 ' then ' ' else D.[Date] end as [Date],
case when H3.Flag= '1 ' then ' ' else D.Bank end as Bank,
case when H3.Flag= '1 ' then ' ' else cast(D.[Sum] as varchar) end as [Sum],
H3.SysID,H3.[Date],H3.[Sum]
from 贷款主表 D
left join
(
select H1.*,isnull(H2.SysID,1) as Flag
from 还款从表 H1
Left Join
(
select SysID,min([Date]) as [Date],min([Sum]) as [Sum]
From 还款从表
group by SysID
) H2 on H1.SysID=H2.SysID and H1.[Date]=H2.[Date] and H1.[Sum]=H2.[Sum]
) H3 on D.SysID=H3.SysID
[解决办法]
提点意见：
感觉lz的表设计上有点问题
我个人认为 表的关系应该这样来建
Create table Banks
(
SysID varchar(10)
Bank varchar(10)
)
Create table 贷款主表
(
SysID varchar(10)
贷款次数 int,
贷款时间 date,
金额 int
)
Create table 还款明细表
(
SysID varchar(10) --与 贷款主表的SysID 相关联
贷款次数 int, --与 贷款主表的贷款次数相关联
还款次数 int,
还款时间 date,
金额 int
)
[解决办法]
create table t1(SysID varchar(20), Date varchar(20), Bank varchar(20), Sum int )
create table t2(SysID varchar(20), Date varchar(20), Sum int )
insert into t1
select
'DK07001 ', '2007-04-01 ' , '中国银行 ',1000
union all select
'DK07002 ', '2007-04-12 ' , '农业银行 ' ,2000
union all select
'DK07003 ', '2007-04-15 ' , '中国银行 ',3000
union all select
'DK07004 ' , '2007-04-22 ' , '中国银行 ' , 4000
union all select
'DK07005 ', '2007-04-26 ', '农业银行 ' , 5000
insert into t2
select
'DK07002 ', '2007-04-10 ' , 200
union all select
'DK07002 ', '2007-04-20 ', 400
union all select
'DK07004 ', '2007-04-26 ', 100
union all select
'DK07004 ', '2007-05-26 ', 500
select SysID, Date,Bank,date,sum =
case
when date1 is null then Sum
when date1 = (select min(d.Date) from t2 d where d.SysID = c.SysID) then Sum
end,date1,sum1 from
(
select a.*,b.date as date1,b.sum as sum1 from t1 a left join t2 b on a.SysID = b.SysID
) c
/*
SysIDDate Bank SumDate1 Sum1
-----------------------------------------------
DK070012007-04-01 中国银行 1000 NULL 0
DK070022007-04-12 农业银行 2000 2007-04-10 200
DK070022007-04-12 农业银行 null 2007-04-20 400
DK070032007-04-15 中国银行 3000 NULL 0
DK070042007-04-22 中国银行 4000 2007-04-26 100
DK070042007-04-22 中国银行 null 2007-05-26 500
DK070052007-04-26 农业银行 5000 NULL 0
*/
