在SQL中，找到总和达到特定数量（或其他表中的amt）的行的组合

在SQL中，找到总和达到特定数量（或其他表中的amt）的行的组合

只是为了好玩，我做了一个蛮力的解决方案。它将找到一个，两个或三个记录的总和

Total_amt

。您可以通过添加

d4

，子

d5

选择等将其扩展为处理每笔交易更多的事务：

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_amtfrom Table_2 tcross join (    select D_ID, Deposit_amt from Table_1 ) d1inner 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 nullinner 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 nullwhere isnull(d1.Deposit_amt, 0) + isnull(d2.Deposit_amt, 0) + isnull(d3.Deposit_amt, 0) = t.Total_amtorder by Total_amtrollback tran

输出：

Total_amt   d1_ID       d1_amt      d2_ID       d2_amt      d3_ID       d3_amt----------- ----------- ----------- ----------- ----------- ----------- -----------43123NULL        NULL44123NULL        NULL414NULL        NULL        NULL        NULL410          4NULL        NULL        NULL        NULL17          912          311417          912          411417          10          4591417          87761417          613          14NULL        NULL17          10          4613          NULL        NULL17          10          4877617          87594117          10          4912          4117          87593117          10          4912          3117          613          312317          613          412323          87613          2323          613          593123          613          594123          10          47661323          10          4912          8723          76613          1423          912          8714(24 row(s) affected)

注意： 您可以过滤出其的各个行

Deposit_amt >Total_amt

，但是除非对其

Deposit_amt

进行索引，否则这可能对性能没有太大帮助。