求一算法

Question

有如下2张表
表一
客户id     欠款日期      欠款金额
1          2010-10-11     3000
2          2010-10-11     5000
表二
客户id     付款日期      付款金额
1          2010-10-12     1000
1          2010-10-13     1000
2          2010-10-15     2000
2          2010-10-16     4000想得到以下结果
客户id    欠款日期    欠款金额    还款日期    还款金额
1          2010-10-11     3000    2010-10-12   1000
1          2010-10-11     2000    2010-10-13   1000
1          2010-10-11     1000
2          2010-10-11     5000    2010-10-15   2000
2          2010-10-11     3000    2010-10-16   3000(这里因为回款大于欠款，只填补欠款金额)    

Answer 1

CREATE TABLE tab1(客户id int, 欠款日期 datetime, 欠款金额 money)
CREATE TABLE tab2(客户id int, 付款日期 datetime, 付款金额 money)
go
INSERT INTO tab1--([客户id], [欠款日期], [欠款金额])
SELECT 1, '2010-10-11', 3000 UNION ALL
SELECT 2, '2010-10-11', 5000INSERT INTO tab2
SELECT 1, '2010-10-12', 500 UNION ALL
SELECT 1, '2010-10-13', 1000 UNION ALL
SELECT 2, '2010-10-15', 2000 UNION ALL
SELECT 2, '2010-10-16', 4000;WITH t AS
( SELECT 客户id, 欠款日期, 欠款金额, 付款日期=cast('' AS DATETIME), [付款金额]=CAST(0.00 AS MONEY), 欠款金额2=欠款金额--CAST(0.00 AS MONEY)
FROM tab1 
--WHERE 欠款金额 IN(3000)

UNION ALL 

SELECT t1.客户id, 欠款日期, 欠款金额, t2.付款日期, t2.[付款金额], t1.[欠款金额2]-t2.[付款金额] 欠款金额2 
FROM t t1
INNER JOIN tab2 t2
ON t1.[客户id] = t2.[客户id] AND t2.付款日期>t1.付款日期
WHERE t1.[欠款金额2]-t2.[付款金额]>-1001

)
SELECT 客户id, 欠款日期, 欠款金额2+付款金额 欠款金额, 付款日期 还款日期, 付款金额 还款金额 
FROM t
WHERE 付款金额>0
ORDER BY 客户id, 欠款金额2 descgo
DROP TABLE tab1
DROP TABLE tab2

Answer 2

--> 测试数据：#1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(客户id int, 欠款日期 datetime, 欠款金额 int)
insert into #1
select 1, '2010-10-11', 3000 union all
select 2, '2010-10-11', 5000
--> 测试数据：#2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2(客户id int, 付款日期 datetime, 付款金额 int)
insert into #2
select 1, '2010-10-12', 1000 union all
select 1, '2010-10-13', 1000 union all
select 2, '2010-10-15', 2000 union all
select 2, '2010-10-16', 4000;with cte as
(
select *, (select sum(付款金额) from #2 where 客户id=t.客户id and 付款日期<=t.付款日期)total from #2 t
union all
select 客户id, null, null, sum(付款金额) from #2 group by 客户id
)
select a.客户id,
欠款日期 = convert(varchar(10),欠款日期,120),
欠款金额 = 欠款金额 - total + isnull(付款金额,0),
付款日期 = isnull(convert(varchar(10),付款日期,120),''),
还款金额 = isnull(ltrim(case when 欠款金额>=total then b.付款金额 else 欠款金额 - total + 付款金额 end),'')
from #1 a join cte b on a.客户id=b.客户id
where 付款金额 is not null or 付款金额 is null and total<欠款金额
order by 1/*
客户id      欠款日期   欠款金额    付款日期   还款金额
----------- ---------- ----------- ---------- ------------
1           2010-10-11 3000        2010-10-12 1000
1           2010-10-11 2000        2010-10-13 1000
1           2010-10-11 1000                   
2           2010-10-11 5000        2010-10-15 2000
2           2010-10-11 3000        2010-10-16 3000
*/

Answer 3

感觉需要的结果使用简单的select语句可以搞定。２楼的或许能解决问题，但那么复杂的查询效率如何，构造它的时间成本呢。解决问题，不能太技术-oriented了。

Answer 4

CREATE TABLE tab1(客户id int, 欠款日期 datetime, 欠款金额 money)
CREATE TABLE tab2(客户id int, 付款日期 datetime, 付款金额 money)INSERT INTO tab1--([客户id], [欠款日期], [欠款金额])
SELECT 1, '2010-10-11', 3000 UNION ALL
SELECT 2, '2010-10-11', 5000INSERT INTO tab2
SELECT 1, '2010-10-12', 500 UNION ALL
SELECT 1, '2010-10-13', 1000 UNION ALL
SELECT 2, '2010-10-15', 2000 UNION ALL
SELECT 2, '2010-10-16', 4000;select 客户id,欠款日期,欠款金额,付款日期,
       case when 付款金额 > 欠款金额
              then 欠款金额
            else 付款金额
       end as 付款金额
from (
select a.客户id,a.欠款日期,
   a.欠款金额 - isnull(( select sum(c.付款金额)
 from   tab2 c
 where  a.客户id = c.客户id
and c.付款日期 < b.付款日期
),0) as 欠款金额 ,      
   b.付款日期,b.付款金额
from   tab1 a
   left outer join ( select * 
 from tab2 
 union all
 select distinct  
客户id,'2100-01-01',0
 from   tab1
   ) b
 on a.客户id = b.客户id
    ) a
where  欠款金额 > 0/*
客户id   欠款日期                  欠款金额  付款日期                   付款金额
------- ----------------------- ------- ----------------------- -------
1 2010-10-11 00:00:00.000 3000.00 2010-10-12 00:00:00.000 500.00
1 2010-10-11 00:00:00.000 2500.00 2010-10-13 00:00:00.000 1000.00
1 2010-10-11 00:00:00.000 1500.00 2100-01-01 00:00:00.000 0.00
2 2010-10-11 00:00:00.000 5000.00 2010-10-15 00:00:00.000 2000.00
2 2010-10-11 00:00:00.000 3000.00 2010-10-16 00:00:00.000 3000.00
*/

Answer 5

以上代码略作修改 select 客户id,欠款日期,欠款金额,付款日期,
       case when 付款金额 > 欠款金额
              then 欠款金额
            else 付款金额
       end as 付款金额
from (
select a.客户id,a.欠款日期,
   a.欠款金额 - isnull(( select sum(c.付款金额)
 from   tab2 c
 where  a.客户id = c.客户id
and c.付款日期 < b.付款日期
),0) as 欠款金额 ,      
   nullif(b.付款日期,'2100-01-01') as 付款日期,b.付款金额
from   tab1 a
   left outer join ( select * 
 from tab2 
 union all
 select distinct  
客户id,'2100-01-01',0
 from   tab1
   ) b
 on a.客户id = b.客户id
    ) a
where  欠款金额 > 0