oracle求SQL语句

[cncmax11]

oracle表结构如下，想随机取出 sum(预存余额）=1000000 的行，语句该怎么写？


"地市分公司","业务类型","ACCT_ID","帐本类型","预存余额"
"0436","","1714550000","普通预存款","100"
"0436","","20030709","普通赠款","100"
"0436","","1716850900","普通预存款","50"
"0436","","1716893000","普通预存款","50"
"0436","","1718511800","普通预存款","10"
"0436","","1720298400","普通预存款","30"
"0436","","159765934","普通赠款","100"
"0436","","1112187296","普通赠款","100"
"0436","","1112229838","普通赠款","100"
"0436","","1312033791","普通赠款","100"
"0436","","1700000733","普通预存款","50"
"0436","","1700000741","普通预存款","50"
"0436","","1700000758","普通预存款","50"
"0436","","1700000822","普通预存款","50"
"0436","","1700023157","普通预存款","100"
"0436","","1710416072","普通预存款","200"
"0436","","1710416268","普通预存款","100"
"0436","","1710416337","普通预存款","100"

[超超王贱贱]

mark，等高手。

[kingwinerscxp]

需明确一下需求，比如按什么做sum分组，随机行要选出来的数据是什么？最好给个样例。


用两层或三层查询，在子查询中可以用分组函数产生一个最大行数据（num2)和行号，再在子查询外面根据随机函数产生行号(dbms_random.value(1,num2)取整)进行关联选取

[304的的哥]

“只要取出SUM(余额)等于100000的记录就行，然后把这些记录删掉。不用每次提取都是固定的记录。”
根据那个字段来sum？
"地市分公司","业务类型","ACCT_ID"还是"帐本类型"?

--获取随机行
SQL> select * from (
  2  select * from emp
  3  order by dbms_random.value)
  4  where rownum<2
  5  /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7902 FORD       ANALYST    7566 1981-12-03    3000.00               20
 
SQL> /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 
SQL> /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7900 JAMES      CLERK      7698 1981-12-03     950.00               30
 
SQL> 

[free1879]

存储过程，一个sql写不出来

[cncmax11]

只要有N条记录sum(预存余额)是100000就行,不管是哪些行，多少行。我就想把这表sum(预存余额)的额度控制一下

[不要悲剧人生]

都随机了，还能查询出固定的行出来吗，每次查询是必须会产生相同的结果吧，（在总数据没有变动的情况下。），你sum用什么来分组呢， 有解吗？ 待高手解答

[304的的哥]

[Quote=引用 8 楼 cncmax11 的回复:]

只要有N条记录sum(预存余额)是100000就行,不管是哪些行，多少行。我就想把这表sum(预存余额)的额度控制一下
[/Quote]
难道你的意思是从表的第一行sum起，一直加到当前行，如果sum结果为100000，那么就返回前面的所有行？
如果是的话，可以使用下面的方法：

SQL> with t as(
  2       select 1 col_1,'aaa' col_2,50 col_3 from dual union all
  3       select 2,'bbb',100 from dual union all
  4       select 3,'ccc',5800 from dual union all
  5       select 4,'ddd',4520 from dual union all
  6       select 5,'eee',10201 from dual union all
  7       select 6,'fff',980 from dual)
  8  select col_1,col_2,col_3 from (
  9  select t.*,sum(col_3) over (order by rownum rows between unbounded preceding and current row) col_4
 10  from t)
 11  where col_4 < 10000
 12  /
 
     COL_1 COL_2      COL_3
---------- ----- ----------
         1 aaa           50
         2 bbb          100
         3 ccc         5800

[304的的哥]

上面的方法是取出sum到当前行结果为指定值的全部行，下面是取出素面到当前行结果为指定值，
并取出sum到指定值的那一行，具体数据你自己测试测试：

SQL> with t as(
  2       select 1 col_1,'aaa' col_2,50 col_3 from dual union all
  3       select 2,'bbb',100 from dual union all
  4       select 3,'ccc',5000 from dual union all
  5       select 120,'adsfasdf',4850 from dual union all
  6       select 4,'ddd',4520 from dual union all
  7       select 5,'eee',10201 from dual union all
  8       select 6,'fff',980 from dual)
  9  select t.*,sum(col_3) over (order by rownum rows between unbounded preceding and current row) col_4
 10  from t
 11  /
 
     COL_1 COL_2         COL_3      COL_4
---------- -------- ---------- ----------
         1 aaa              50         50
         2 bbb             100        150
         3 ccc            5000       5150
       120 adsfasdf       4850      10000
         4 ddd            4520      14520
         5 eee           10201      24721
         6 fff             980      25701
 
7 rows selected
 
SQL> 
SQL> with t as(
  2       select 1 col_1,'aaa' col_2,50 col_3 from dual union all
  3       select 2,'bbb',100 from dual union all
  4       select 3,'ccc',5000 from dual union all
  5       select 120,'adsfasdf',4850 from dual union all
  6       select 4,'ddd',4520 from dual union all
  7       select 5,'eee',10201 from dual union all
  8       select 6,'fff',980 from dual)
  9  select col_1,col_2,col_3 from (
 10  select t.*,sum(col_3) over (order by rownum rows between unbounded preceding and current row) col_4
 11  from t)
 12  where col_4=10000
 13  /
 
     COL_1 COL_2         COL_3
---------- -------- ----------
       120 adsfasdf       4850
 

[cncmax11]

只要取出SUM(余额)等于100000的记录就行，然后把这些记录删掉。不用每次提取都是固定的记录。

[秋雨飘落]

用存储过程实现吧，慢慢判断

先随机取出2条，求和，然后判断差距，慢慢加

[小灰狼W]

这个大概得用存储过程
要做到随机，还有可能不存在这样的查询结果