가격이라는 필드 100개중 실제로는 21개 각각(2,000 3,420 10,000)이러한 금액들이 있습니다.
첫번째부터 21개를 합산해보니 99,840원이라는 값이 나오고 22개를 합산하면 112,100원이 나오네요
그렇다면 21개99,840원이 100,000원에 가깝네요
구하고자하는 select 는 100,000원에 가장근사치가 될수있는 합이 1번부터 몇개까지를 더하면 되는지...
만약21개라면 21개의 필드가 출력됐으면 좋겠습니다...
쿼리문이 있을 까요... 아시는분들 좀 갈켜주세요...^^
어떤분이 이런 답변을 보내주셨는데 뭔지 잘 모르겠어요...
declare @amt int
set @amt = 100000 ;
with test
as (
select 1 no ,6347 amt union all
select 2 no ,7047 amt union all
select 3 no ,7843 amt union all
select 4 no ,4156 amt union all
select 5 no ,4040 amt union all
select 6 no ,971 amt union all
select 7 no ,1707 amt union all
select 8 no ,374 amt union all
select 9 no ,6779 amt union all
select 10 no ,7361 amt union all
select 11 no ,952 amt union all
select 12 no ,3527 amt union all
select 13 no ,1361 amt union all
select 14 no ,3693 amt union all
select 15 no ,2857 amt union all
select 16 no ,1996 amt union all
select 17 no ,8418 amt union all
select 18 no ,7291 amt union all
select 19 no ,3479 amt union all
select 20 no ,6918 amt union all
select 21 no ,1655 amt union all
select 22 no ,9835 amt union all
select 23 no ,1453 amt union all
select 24 no ,1877 amt union all
select 25 no ,8930 amt union all
select 26 no ,8416 amt union all
select 27 no ,982 amt union all
select 28 no ,2400 amt union all
select 29 no ,786 amt union all
select 30 no ,937 amt union all
select 31 no ,5494 amt union all
select 32 no ,1283 amt union all
select 33 no ,2528 amt union all
select 34 no ,768 amt union all
select 35 no ,902 amt union all
select 36 no ,7418 amt union all
select 37 no ,368 amt union all
select 38 no ,7712 amt union all
select 39 no ,7168 amt union all
select 40 no ,797 amt union all
select 41 no ,2304 amt union all
select 42 no ,2968 amt union all
select 43 no ,2023 amt union all
select 44 no ,1118 amt union all
select 45 no ,3894 amt union all
select 46 no ,2476 amt union all
select 47 no ,501 amt union all
select 48 no ,4633 amt union all
select 49 no ,5956 amt union all
select 50 no ,7321 amt union all
select 51 no ,6152 amt union all
select 52 no ,5206 amt union all
select 53 no ,7898 amt union all
select 54 no ,8820 amt union all
select 55 no ,4052 amt union all
select 56 no ,1269 amt union all
select 57 no ,1224 amt union all
select 58 no ,4963 amt union all
select 59 no ,454 amt union all
select 60 no ,2816 amt union all
select 61 no ,1017 amt union all
select 62 no ,309 amt union all
select 63 no ,3829 amt union all
select 64 no ,8649 amt union all
select 65 no ,825 amt union all
select 66 no ,6911 amt union all
select 67 no ,1932 amt union all
select 68 no ,2454 amt union all
select 69 no ,3227 amt union all
select 70 no ,9941 amt union all
select 71 no ,2139 amt union all
select 72 no ,526 amt union all
select 73 no ,136 amt union all
select 74 no ,8667 amt union all
select 75 no ,5222 amt union all
select 76 no ,4016 amt union all
select 77 no ,8423 amt union all
select 78 no ,3018 amt union all
select 79 no ,4238 amt union all
select 80 no ,4708 amt union all
select 81 no ,9240 amt union all
select 82 no ,8715 amt union all
select 83 no ,881 amt union all
select 84 no ,4286 amt union all
select 85 no ,1666 amt union all
select 86 no ,2225 amt union all
select 87 no ,5491 amt union all
select 88 no ,8360 amt union all
select 89 no ,7187 amt union all
select 90 no ,5515 amt union all
select 91 no ,9801 amt union all
select 92 no ,1857 amt union all
select 93 no ,7876 amt union all
select 94 no ,8420 amt union all
select 95 no ,6287 amt union all
select 96 no ,4061 amt union all
select 97 no ,1899 amt union all
select 98 no ,8373 amt union all
select 99 no ,6045 amt union all
select 100 no ,5209 amt ) ,
result as (
select a.no , abs(sum(b.amt) - @amt) absamt
from test a, test b
where a.no >= b.no
group by a.no )
select *
from test
where no <= ( select no
from result
where absamt = ( select min(absamt)
from result ) ) |