编号1 编号2 数量
1 1 1
1 2 1
1 3 1
2 4 1
2 5 1
2 6 1
3 7 1
3 8 1
4 9 1
4 10 1
传入一个参数，按照编号一排序，然后从小到大依次将“数量”字段加和，获取加和刚好大于传入参数的最少的前几项数据，例如：
传入参数 3 得到结果
1 1 1
1 2 1
1 3 1
传入参数 4 得到结果
1 1 1
1 2 1
1 3 1
2 4 1
2 5 1
2 6 1
传入参数 7 得到结果
1 1 1
1 2 1
1 3 1
2 4 1
2 5 1
2 6 1
3 7 1
3 8 1
------解决方案--------------------
- SQL code
select * from ttt where rownum < 3unionselect * from ttt where a = (select 编号1 from (select 编号1, rownum n from ttt) where n = 3)
------解决方案--------------------
LZ等于7的情况应该会SELECT出来全部记录。 1+2+3+4+5+6 = 21
- SQL code
TRY IT ..SQL> SELECT * FROM TEST_NUM; NUM1 NUM2 QTY---------- ---------- ---------- 1 1 1 1 2 1 1 3 1 2 4 1 2 5 1 2 6 1 3 7 1 3 8 1 4 9 1 4 10 110 rows selected-- ENTER_NUM = 3SQL> SELECT * 2 FROM TEST_NUM 3 WHERE NUM2 <= (SELECT SUM(DECODE(SIGN(&ENTER_NUM - NUM2), 1, NUM2, 0)) 4 FROM TEST_NUM); NUM1 NUM2 QTY---------- ---------- ---------- 1 1 1 1 2 1 1 3 1-- ENTER_NUM = 4SQL> SELECT * 2 FROM TEST_NUM 3 WHERE NUM2 <= (SELECT SUM(DECODE(SIGN(&ENTER_NUM - NUM2), 1, NUM2, 0)) 4 FROM TEST_NUM); NUM1 NUM2 QTY---------- ---------- ---------- 1 1 1 1 2 1 1 3 1 2 4 1 2 5 1 2 6 16 rows selected-- ENTER_NUM = 7SQL> SELECT * 2 FROM TEST_NUM 3 WHERE NUM2 <= (SELECT SUM(DECODE(SIGN(&ENTER_NUM - NUM2), 1, NUM2, 0)) 4 FROM TEST_NUM); NUM1 NUM2 QTY---------- ---------- ---------- 1 1 1 1 2 1 1 3 1 2 4 1 2 5 1 2 6 1 3 7 1 3 8 1 4 9 1 4 10 110 rows selectedSQL>
------解决方案--------------------
- SQL code
select * from table where rownum <= (select rr from (select sum(数量) over(partition by 1 order by rownum) rn, rownum rr from table t order by 编号1, 编号2) where rn > [color=#FF0000]10[/color] and rownum = 1) order by 编号1, 编号2