sql 检索问题

Question

编号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

Answer 1

select *
  from ttt
 where rownum < 3
union
select *
  from ttt
 where a = (select 编号1 from (select 编号1, rownum n from ttt) where n = 3)
参数为几的话就把3改为几，ttt为表名

Answer 2

LZ等于7的情况应该会SELECT出来全部记录。  1+2+3+4+5+6 = 21
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 = 3
SQL> 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 = 4
SQL> 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 = 7
SQL> 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> 

Answer 3

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 > 10
                     and rownum = 1)
 order by 编号1, 编号2
10为你传入的参数