Total amount of money

Original post:

Http://community.9cbs.net/expert/topic/3190/3190686.xml?temp=.6296961

Table Test Record: AA BB 001 50.5 002 60 003 15.4 004 25 005 48 ...

Enter any amount, then find if there is a total of the amount or several records equivalent to this amount. For example, enter 25, you have to find 004, enter 85, then find out 002 and 004, and push it according to the time. -------------------------------------------------- ----------------------------------

- Test data Create Table test (aa varchar (10), BB Numeric (10, 2)) Insert test select '001', 50.5UNION All SELECT '003', 15.4UNION All SELECT '004 ', 25UNION All Select' 005 ', 48UNION All SELECT' 006, 37GO

- Query function crete function fn_search (@num numeric (10, 2)) Returns @r Table (aa varcha (10), bb Numeric (10, 2)) asbegin declare @t table (aa varchar (8000), AA1 VARCHAR (10), BB Numeric (10, 2), Level Int) Declare @L INT

INSERT @R SELECT AA, BB from Test where bb = @ Num if @@ RowCount> 0 goto lb_exit

Set @ l = 0 insert @t select ',' aa ',', aa, bb, @ l from test where bb <@num while @@ rowcount> 0 begin insert @R select distinct a.aa, a.bb From test a, (Select A.AA, A.BB, AA1 = B.AA from test a, @ Tb where b.level=@l and b.aa1 0 if @@ rowcount> 0 goto lb_exit

Set @ L = @ L 1 INSERT @t SELECT B.AA A.AA ',', A.AA, A.BB B.BB, @ l from test a, @ TB where b.level=@l -1 and B.AA1

LB_EXIT: RETURNENDGO

- Call Test 1Select * from dbo.fn_search (25)

/*--result

AA BB ---------- ------------ 004 25.00

(The number of rows affects is 1 line) - * /

- Call Test 2Select * from dbo.fn_search (135.5)

/*--result

AA BB ---------------------- 001 50.50002 60.00004 25.00005 48.00006 37.00

(The number of rows affects is 5 lines) - * /

- Call Test 3 (Can't find, no return value) Select * from dbo.fn_search (135.7)

/ * - Result aa bb --------------------

(The number of rows affects is 0) - * / GO

Drop Table TestDrop Function Fn_Search