-- 合同表   
create table contract (
    contract_id char(12) primary key,
    contract_code int,
    name nvarchar(32)
)
insert into contract
select '200700086187',1811,'张三' union
select '200700086188',1813,'张二' union
select '200700086189',1816,'张五' union
select '200700086190',1819,'张九' 

-- 计划付款表   
create table plan_pay (
    pay_plan_code int ,
    contract_code int ,
    plan_date datetime ,
    pay_price money
)
insert into plan_pay
select 6378, 1811, '2007-03-22',152050.00 union
select 6379, 1811, '2007-04-30',150000.00 union
select 6380, 1811, '2007-05-30',204738.00 union
select 6388, 1813, '2007-03-22',152050.00 union
select 6389, 1813, '2007-04-30',150000.00 union
select 6390, 1816, '2007-05-30',204738.00

-- 收款计划表   
create table account_plan (
    account_plan_code int ,
    account_code int,
    plan_code int,
    price money
)
insert into account_plan 
select 5609, 3923, 6378, 152050.00 union
select 6026, 4154, 6379, 150000.00 union
select 6025, 4237, 6380, 44738.00

-- 收款表   
create table account (
    account_code int ,
    account_date datetime ,
    account_price money
)
insert into account 
select 3923, '2007-03-22',152050.00 union
select 4154, '2007-05-31',150000.00 union
select 4237, '2007-07-02',44738.00
go

Select a.*,b.pay_price,
       Datediff(day,b.plan_date,getdate()) as 逾期天数
 from contract as a Inner Join plan_pay as b 
      on a.contract_code=b.contract_code 
      inner Join account_plan  as c 
       on b.plan_code=c.plan_code 
Where not exists(Select * from account 
    Where account_code=c.account_code)

select c.contract_id,c.contract_code,c.name,
p.plan_date,p.pay_price,
datediff(day,p.plan_date,getdate()) as 逾期天
from contract c,plan_pay p
where c.contract_code=p.contract_code
and p.plan_date<getdate()
and not exists (
    select 1 from account_plan n,account a
    where a.account_code=n.account_code
    and n.plan_code=p.pay_plan_code
    )

select c.contract_id,c.contract_code,c.name,
p.plan_date,p.pay_price,
datediff(day,p.plan_date,getdate()) as 逾期天
from contract c,plan_pay p
where c.contract_code=p.contract_code
and p.plan_date<getdate()
and not exists (
    select 1 from account_plan n,account a
    where a.account_code=n.account_code
    and n.plan_code=p.pay_plan_code
    )

-- 合同表   
create table contract (
    contract_id char(12) primary key,
    contract_code int,
    name nvarchar(32)
)
insert into contract
select '200700086187',1811,'张三' union
select '200700086188',1813,'张二' union
select '200700086189',1816,'张五' union
select '200700086190',1819,'张九' 

-- 计划付款表   
create table plan_pay (
    pay_plan_code int ,
    contract_code int ,
    plan_date datetime ,
    pay_price money
)
insert into plan_pay
select 6378, 1811, '2007-03-22',152050.00 union
select 6379, 1811, '2007-04-30',150000.00 union
select 6380, 1811, '2007-05-30',204738.00 union
select 6388, 1813, '2007-03-22',152050.00 union
select 6389, 1813, '2007-04-30',150000.00 union
select 6390, 1816, '2007-05-30',204738.00

-- 收款计划表   
create table account_plan (
    account_plan_code int ,
    account_code int,
    plan_code int,
    price money
)
insert into account_plan 
select 5609, 3923, 6378, 152050.00 union
select 6026, 4154, 6379, 150000.00 union
select 6025, 4237, 6380, 44738.00

-- 收款表   
create table account (
    account_code int ,
    account_date datetime ,
    account_price money
)
insert into account 
select 3923, '2007-03-22',152050.00 union
select 4154, '2007-05-31',150000.00 union
select 4237, '2007-07-02',44738.00
go
select d.*,datediff(day,p.plan_date,getdate()) as 逾期天
from contract d 
right join

(select * from plan_pay c
where not exists(
select 1
from account_plan a
left join account b on a.account_code = b.account_code
where a.plan_code = c.pay_plan_code)) p
on d.contract_code = p.contract_code

drop table contract,plan_pay,account_plan,account

/*

contract_id  contract_code name                             逾期天         
------------ ------------- -------------------------------- ----------- 
200700086188 1813          张二                               300
200700086188 1813          张二                               261
200700086189 1816          张五                               231

（所影响的行数为 3 行）
*/