以下是很多张订单的金额,我手上有一张发票金额为59181.83
我想知道是哪些订单的金额相加正好等于这张发票的金额,
请问在SQL可以做出来吗?
650.79
1,518.51
57.62
460.98
518.60
691.47
24,075.00
56,175.00
65,474.51
73,658.82
8,184.31
98,211.77
57.62
57.62
57.62
8,184.31
8,184.31
8,184.31
57.62
57.62
807.00
8,184.31
8,184.31
57.62
57.62
57.62
57.62
57.62
57.62
57.62
-----------------------
我想知道是哪些订单的金额相加正好等于这张发票的金额,
请问在SQL可以做出来吗?
650.79
1,518.51
57.62
460.98
518.60
691.47
24,075.00
56,175.00
65,474.51
73,658.82
8,184.31
98,211.77
57.62
57.62
57.62
8,184.31
8,184.31
8,184.31
57.62
57.62
807.00
8,184.31
8,184.31
57.62
57.62
57.62
57.62
57.62
57.62
57.62
-----------------------
想用SQL应该速度更快的,只是现在没思路
各位请帮帮忙!
先谢谢大家了!
create table tb(qnt float)insert into tb values(650.79)
insert into tb values(1518.51)
insert into tb values(57.62 )
insert into tb values(460.98 )
insert into tb values(518.60 )
insert into tb values(691.47 )
insert into tb values(24075.00 )
insert into tb values(56175.00 )
insert into tb values(65474.51 )
insert into tb values(73658.82 )
insert into tb values(8184.31 )
insert into tb values(98211.77 )
insert into tb values(57.62 )
insert into tb values(57.62 )
insert into tb values(57.62 )
insert into tb values(8184.31 )
insert into tb values(8184.31 )
insert into tb values(8184.31 )
insert into tb values(57.62 )
insert into tb values(57.62 )
insert into tb values(807.00 )
insert into tb values(8184.31 )
insert into tb values(8184.31 )
insert into tb values(57.62 )
insert into tb values(57.62 )
insert into tb values(57.62 )
insert into tb values(57.62 )
insert into tb values(57.62 )
insert into tb values(57.62 )
insert into tb values(57.62 )select * from tb
两个订单是50,这种是没有关系的,我只要找出这个数字,实际操作中我自己挑一张出来做账就可以了
SQL不知道怎么写
假定你知道至多三个订单可以得出你的发票金额,你可以用以下代码穷举出来;如果要更多的,不停的加,d4,d5,d6就是了。
这个实际上也是你的数组法。begin trancreate table Table_1 (D_ID int, Deposit_amt int)
create table Table_2 (Total_ID int, Total_amt int)insert into Table_1 (D_ID, Deposit_amt) values (1, 4)
insert into Table_1 (D_ID, Deposit_amt) values (2, 3)
insert into Table_1 (D_ID, Deposit_amt) values (3, 1)
insert into Table_1 (D_ID, Deposit_amt) values (4, 1)
insert into Table_1 (D_ID, Deposit_amt) values (5, 9)
insert into Table_1 (D_ID, Deposit_amt) values (6, 13)
insert into Table_1 (D_ID, Deposit_amt) values (7, 6)
insert into Table_1 (D_ID, Deposit_amt) values (8, 7)
insert into Table_1 (D_ID, Deposit_amt) values (9, 12)
insert into Table_1 (D_ID, Deposit_amt) values (10, 4)insert into Table_2 (Total_ID, Total_amt) values (1, 17)
insert into Table_2 (Total_ID, Total_amt) values (2, 23)
insert into Table_2 (Total_ID, Total_amt) values (3, 55)
insert into Table_2 (Total_ID, Total_amt) values (4, 4)select t.Total_amt,
d1.D_ID as d1_ID, d1.Deposit_amt as d1_amt,
d2.D_ID as d2_ID, d2.Deposit_amt as d2_amt,
d3.D_ID as d3_ID, d3.Deposit_amt as d3_amt
from Table_2 t
cross join (
select D_ID, Deposit_amt from Table_1
) d1
inner join (
select D_ID, Deposit_amt from Table_1
union all
select null, null
) d2 on d1.D_ID > d2.D_ID or d2.D_ID is null
inner join (
select D_ID, Deposit_amt from Table_1
union all
select null, null
) d3 on d2.D_ID > d3.D_ID or d3.D_ID is null
where isnull(d1.Deposit_amt, 0) + isnull(d2.Deposit_amt, 0) + isnull(d3.Deposit_amt, 0) = t.Total_amt
order by Total_amtrollback tran