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"
"地市分公司","业务类型","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"
用两层或三层查询,在子查询中可以用分组函数产生一个最大行数据(num2)和行号,再在子查询外面根据随机函数产生行号(dbms_random.value(1,num2)取整)进行关联选取
根据那个字段来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>
如果是的话,可以使用下面的方法: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
并取出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