ايجاد ركورد

[salimim]

در جدولي فيلد عددي شامل تعدادي عدد وجود دارد
مي خوام در جدول ديگري تمام تركيبهاي جمعهاي اين اعداد را insert كنم
مثال: اگر اعداد
5
7
9
1
باشند، اين اعداد insert شوند:
5
12(5+7)
21(5+7+9)
22(5+7+9+1)
7
16(9+7)
17(7+9+1)
9
10(9+1)
1
15(5+9+1)
6(5+1)
8(7+1)

[محمد سلیم آبادی]

جالبه! پازل طرح می کنن. جمع 5 و 9 را از قلم انداختین.
براتون یک روش نوشتم ولی فکر نمی کنم بهترین باشه


declare @t table (i int)
insert into @t values (5),(7),(9),(1)

--insert into your_table
select i from @t
union
select distinct a.i+b.i
from @t as a
cross join @t as b
where a.i <> b.i
union
select distinct a.i+b.i+c.i
from @t as a
cross join @t as b
cross join @t as c
where a.i <> b.i and a.i <> c.i
and b.i <> c.i
union
select sum(i)
from @t
order by i

[محمد سلیم آبادی]

روش Itzik Ben-Gan (http://www.sqlmag.com/Article/ArticleID/101607/sql_server_101607.html)


with nums as
(
select 5 as n
union all select 7
union all select 9
union all select 1
),
formulas as
(
select
cast(n as varchar(1000)) as formula,
n,
n as total
from nums
where n <= 9
union all
select
cast(formula + ' + ' + cast(cur.n as varchar(10)) as varchar(1000)),
cur.n,
prv.total + cur.n
from formulas as prv
join nums as cur
on cur.n > prv.n
where prv.total + cur.n <= 100
)
select formula + ' = ' + cast(total as varchar(10)) as formula
from formulas