有难度，请高手帮助。求SQL语句
合同表：contract
ID Amount
1 2000
2 1000
3 1200
4 1350
5 800
收款明细：collection
ID Amount OverDue(过期收款标志)
1 1000 Y
2 1000 N
3 500 Y
4 500 N
5 1000 Y
6 1550 N
7 800 Y
如何得到：contract_collection
ID OverDueAmount(过期收款) NaturalAmount(自然收款)
1 1000 1000
2 500 500
3 200 1000
4 1350 0
5 0 800
即：按 收款顺序和合同顺序 生成 每个合同的 自然收款和过期收款 情况
问题点数：100、回复次数：34Top
1 楼fcuandy（了此残生.）回复于 2006-07-10 13:21:12 得分 0
按你的数据猜了第一条结果是怎么来的，可看到第二条，实在想不出那两个500是哪来的。Top
2 楼thordon（索尔的吼声）回复于 2006-07-10 13:27:22 得分 1
用CASE 做应该 可以
你查一下帮助吧
手上没有SQL-SERVER 没法写Top
3 楼jjhlover（菜花）回复于 2006-07-10 13:27:51 得分 0
回楼上的话：
是从“收款明细”的3、4 来的Top
4 楼Haiwer（海阔天空）回复于 2006-07-10 13:28:03 得分 3
例子搞错了
应该：
ID OverDueAmount(过期收款) NaturalAmount(自然收款)
1 1000 1000
2 500 500
3 1000 200
4 0 1350
5 800 0
Top
5 楼jjhlover（菜花）回复于 2006-07-10 13:28:38 得分 0
TO：fcuandy(要学的东西还很多)
是从“收款明细”的3、4 来的
Top
6 楼Haiwer（海阔天空）回复于 2006-07-10 13:29:08 得分 1
case when 搞不定
Top
7 楼LouisXIV（夜游神）回复于 2006-07-10 13:30:49 得分 1
2个500是1000分2次（即下面的表的3，4行数据）来的Top
8 楼jjhlover（菜花）回复于 2006-07-10 13:31:42 得分 0
TO：Haiwer(海阔天空)
例子错在那里？我又看了，没错呀
Top
9 楼LouisXIV（夜游神）回复于 2006-07-10 13:32:51 得分 1
写游标还凑合Top
10 楼jjhlover（菜花）回复于 2006-07-10 13:33:40 得分 0
2个500是1000分2次（即下面的表的3，4行数据）来的
LouisXIV(夜游神) 说地对。
Top
11 楼LouisXIV（夜游神）回复于 2006-07-10 13:33:41 得分 1
to lz
Haiwer的意思是
Y和N哪个是自然，哪个是过期Top
12 楼jjhlover（菜花）回复于 2006-07-10 13:34:24 得分 0
“Y”是过期Top
13 楼thordon（索尔的吼声）回复于 2006-07-10 13:36:31 得分 1
少一列吧 有类似于编号之类的应该可以吧 用 GROUP BY 做个汇总 当中有CASE 分别求好
然后做个JOINTop
14 楼LouisXIV（夜游神）回复于 2006-07-10 13:37:18 得分 1
如果Y是过期，那么你的例子就反了
Haiwer的正确Top
15 楼thordon（索尔的吼声）回复于 2006-07-10 13:38:23 得分 1
可能在GROUP BY 做的时候 还要调整一下 不过不用用游标做的吧.Top
16 楼jjhlover（菜花）回复于 2006-07-10 13:39:25 得分 0
是，我的错了。Top
17 楼LouisXIV（夜游神）回复于 2006-07-10 13:40:06 得分 1
to thordon(索尔的吼声)
-------------------------------
1.没有相关列，无法做链接查询
2.不是单纯的累积，需要考虑收款不足以及过剩的情况
不适用Case和Group By可以简单完成的Top
18 楼jjhlover（菜花）回复于 2006-07-10 13:40:49 得分 0
纠正例子：
如何得到：contract_collection
ID NaturalAmount(自然收款) OverDueAmount(过期收款)
1 1000 1000
2 500 500
3 200 1000
4 1350 0
5 0 800
即：按 收款顺序和合同顺序 生成 每个合同的 自然收款和过期收款 情况Top
19 楼LouisXIV（夜游神）回复于 2006-07-10 13:41:46 得分 1
不用游标用循环当然也可以做，本质上没有什么区别
至少我是写不出一句Select出结果的写法Top
20 楼jjhlover（菜花）回复于 2006-07-10 13:43:46 得分 0
用游标可以做，但效率可能会不高。Top
21 楼jjhlover（菜花）回复于 2006-07-10 13:44:43 得分 0
收款明细如果是负数，应该更难了Top
22 楼thordon（索尔的吼声）回复于 2006-07-10 14:02:16 得分 1
嗯 可能偶理解有问题 不过感觉照现在的结构没有相关列 跟本没法做的啊 用游标也分不清啊Top
23 楼jjhlover（菜花）回复于 2006-07-10 14:06:46 得分 0
用游标处理。我认为只能模拟人手工操作，按收款一条条处理。Top
24 楼vfp_system（菜鸟一个）回复于 2006-07-10 14:16:24 得分 1
数据之间的链接关系不明确？建议把业务逻辑及两表之间链接的字段说明。Top
25 楼fcuandy（了此残生.）回复于 2006-07-10 14:26:10 得分 1
这个意思啊， 以前回过一个贴。要求大置相似。不过他表中没有连续的ID列，后来用了两次还是三次临时表解决了。Top
26 楼playwarcraft（时间就像乳沟,挤挤还是有的）回复于 2006-07-10 14:27:51 得分 1
暈...粗略考慮了一下,太複雜了 *_*Top
27 楼vfp_system（菜鸟一个）回复于 2006-07-10 15:16:29 得分 2
如果没有逻辑运算关系，可以考虑用交叉报表的功能来实现，大致的方法如下：
select a.id,case b.OverDue
when 'Y' then amount else 0
end as OverDueAmount,
b.overDue
when 'N' then amount else 0
end as NaturalAmount
from contract a,collection b
where a.id=b.id
没有运行过，大致就这样。可以去邹健的BLOG看看，有个交叉报表的存储过程例子。Top
28 楼jjhlover（菜花）回复于 2006-07-10 15:53:08 得分 0
谢谢以前回复，问题还没有解决。我正努力用游标来实现。请大家给出好的意见Top
29 楼godfather_wang（）回复于 2006-07-10 17:30:02 得分 1
有点复杂，是不是财务软件的东东啊！！！！数据结构不是很合理哦！！！！contract和collection表应该有一个用id来对应的东东。没有的话好像不是太好做。
Top
30 楼fcuandy（了此残生.）回复于 2006-07-10 17:45:34 得分 80
/*
--本来想一条语句写完的，结果今天杂事太多，都没时间上来，
--只能做会写会，想写个一句话的是不可能了，一不小心就写错了
--谁要是有心的话，把这些临时表全用子查询代替,这里写临时表
--仅仅是为了简化语句，豪无其它作用.不过，如果写成一条语句
--的话，估计这一条语句在1024*1280的分辨率下要打上好几页^^
CREATE TABLE contract(ID INT IDENTITY(1,1),Amount INT)
INSERT contract(Amount)
SELECT 2000
UNION ALL SELECT 1000
UNION ALL SELECT 1200
UNION ALL SELECT 1350
UNION ALL SELECT 800
CREATE TABLE collection(ID INT IDENTITY(1,1),Amount INT,OverDue CHAR(1))
INSERT collection(Amount,OverDue)
SELECT 1000 ,'Y'
UNION ALL SELECT 1000, 'N'
UNION ALL SELECT 500 , 'Y'
UNION ALL SELECT 500 , 'N'
UNION ALL SELECT 1000 , 'Y'
UNION ALL SELECT 1550 , 'N'
UNION ALL SELECT 800 , 'Y'*/
SELECT ID aid,Amount cAmount,aAmount=(SELECT SUM(Amount) FROM contract b WHERE b.id<=a.id) INTO #ta FROM contract a
SELECT ID bid,Amount sglAmount,bAmount=(SELECT SUM(Amount) FROM collection b WHERE b.id<=a.id) INTO #tb FROM collection a
SELECT aid ,aAmount,bid,bAmount,cAmount,c.Amount sglAmount,id, Amount,OverDue,lAmount=bAmount-aAmount INTO #base
FROM #ta m
INNER JOIN #tb n
ON bAmount>=aAmount AND 1>(SELECT COUNT(1) FROM #tb a WHERE a.bAmount>=m.aAmount AND a.bid<n.bid)
INNER JOIN collection c
ON id<=bid
SELECT aid,aAmount,bAmount,cAmount,sglAmount,id,OverDue,lAmount INTO #s FROM #base a WHERE id>(SELECT ISNULL(MAX(bid),0) FROM #base b WHERE b.bid<a.bid)
SELECT aid,SUM(Overed),SUM(NoOver) FROM
(SELECT aid,
Overed=
CASE OverDue WHEN 'Y' THEN
CASE WHEN aAmount=bAmount THEN
CASE WHEN CurAmount>=sglAmount THEN
CASE WHEN CurAmount>=cAmount
THEN cAmount
ELSE sglAmount
END
ELSE CurAmount END
ELSE
CASE WHEN CurAmount>=cAmount THEN cAmount
ELSE
CASE WHEN CurAmount>=sglAmount THEN sglAmount
ELSE CurAmount END
END
END
ELSE
0
END
,
NoOver=
CASE OverDue WHEN 'N' THEN
CASE WHEN CurAmount>=sglAmount THEN
CASE WHEN 0<(SELECT COUNT(1) FROM
(SELECT * ,CurAmount=sglAmount + (CASE OverDue WHEN 'Y' THEN (SELECT ISNULL(MAX(b.lAmount),0) FROM #s b WHERE b.aid+1=a.aid) ELSE 0 END) FROM #s a)
a WHERE a.aid=base.aid AND a.CurAmount>=a.cAmount AND a.OverDue='Y')
THEN 0
ELSE
(SELECT ISNULL(MAX(a.cAmount)-SUM(a.sglAmount),0) FROM #s a WHERE a.aid=base.aid AND a.OverDue='Y')
END
ELSE CurAmount END
ELSE
0
END
FROM
(SELECT * ,CurAmount=sglAmount + (CASE OverDue WHEN 'Y' THEN (SELECT ISNULL(MAX(b.lAmount),0) FROM #s b WHERE b.aid+1=a.aid) ELSE 0 END) FROM #s a) base
) y
GROUP BY aid
--DROP TABLE contract
--DROP TABLE collection
DROP TABLE #s
DROP TABLE #base
DROP TABLE #ta
DROP TABLE #tbTop
31 楼fcuandy（了此残生.）回复于 2006-07-10 17:47:39 得分 0
找那天写过的那个SQL，找了好久没找到只能重写Top
32 楼jjhlover（菜花）回复于 2006-07-10 21:20:24 得分 0
谢谢：fcuandy(要学的东西还很多)
兄弟，辛苦了。谢谢！Top
33 楼fcuandy（了此残生.）回复于 2006-07-10 21:57:33 得分 0
不用谢。我是来蹭分的，呵呵Top
34 楼jjhlover（菜花）回复于 2006-07-11 09:43:20 得分 0
/*
问题已解决。
谢谢以上兄弟的参与。
特别感谢：fcuandy(要学的东西还很多) 。
以下是我利用游标写的代码，可能会对有同样问题的朋友有用。
*/
-- CREATE TABLE contract(ID INT IDENTITY(1,1),Amount INT)
-- INSERT contract(Amount)
-- SELECT 2000
-- UNION ALL SELECT 1000
-- UNION ALL SELECT 1200
-- UNION ALL SELECT 1350
-- UNION ALL SELECT 800
--
-- CREATE TABLE collection(ID INT IDENTITY(1,1),Amount INT,OverDue CHAR(1))
-- INSERT collection(Amount,OverDue)
-- SELECT 1000 ,'Y'
-- UNION ALL SELECT 1000, 'N'
-- UNION ALL SELECT 500 , 'Y'
-- UNION ALL SELECT 500 , 'N'
-- UNION ALL SELECT 1000 , 'Y'
-- UNION ALL SELECT 1550 , 'N'
-- UNION ALL SELECT 800 , 'Y'
--
-- create table contract_collection (id INT,AmountA int,AmountB int)
--
-- select * from contract
-- select * from collection
-- select * from contract_collection
declare @Amount int
declare @Overdue char(1)
declare collection cursor for select amount,overdue from collection order by id
declare @CAmount int
declare @CId int
declare contract cursor for select id, amount from contract order by id
declare @var int
declare @A int
Declare @B int
select @var = 0
open contract
open collection
while 1=1
begin
fetch next from collection into @Amount,@Overdue
if @@fetch_status=-1 break
while 1=1
begin
if @Amount=0 break
if @var = 0
begin
fetch next from contract into @Cid,@CAmount
if @@fetch_status=-1 break
if not exists(select * from contract_collection where id = @cid)
insert into contract_collection(id) values(@cid)
select @a = AmountA,@b = AmountB from contract_collection where id = @CId
select @a = isnull(@a,0)
select @b = isnull(@b,0)
select @var = @CAmount - @a - @b
end
select @Cid,@CAmount,@Amount,@Overdue
if @var > 0
begin
if @var >= @Amount
begin
update contract_collection set AmountA = (case @Overdue when 'Y' then isnull(AmountA,0)+@Amount else AmountA end),AmountB = (case @Overdue when 'N' then isnull(AmountB,0)+@Amount else AmountB end)
where id = @CId
select @var = @var - @Amount
select @Amount = 0
end
else
begin
update contract_collection set AmountA = (case @Overdue when 'Y' then isnull(AmountA,0)+@var else AmountA end),AmountB = (case @Overdue when 'N' then isnull(AmountB,0)+@var else AmountB end)
where id = @CId
select @Amount = @Amount - @var
select @var = 0
end
end
end
end
close contract
close collection
deallocate contract
deallocate collection
select * from contract_collectionTop
相关问题
关键词
得分解答快速导航
相关链接
广告也精彩
反馈
