SQL> with tab as(select 111 id, 'aaa' name, '代理' type from dual union all 2 select 111, 'aaa', '其他' from dual) 3 select decode(rn,1,id,null),decode(rn,1,name,null),type from ( 4 select id,name ,type ,row_number()over(partition by id,name order by type) rn from tab);
DECODE(RN,1,ID,NULL) DECODE(RN,1,NAME,NULL) TYPE -------------------- ---------------------- ---- 111 aaa 代理 其他
Connected to: Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> break on deptno; SQL> select deptno,empno,ename,sal from emp order by deptno; DEPTNO EMPNO ENAME SAL ---------- ---------- ---------- ---------- 10 7782 CLARK 2450 7839 KING 5000 7934 MILLER 1300 20 7566 JONES 2975 7902 FORD 3000 7876 ADAMS 1100 7369 SMITH 800 7788 SCOTT 3000 30 7521 WARD 1250 7844 TURNER 1500 7499 ALLEN 1600 7900 JAMES 950 7698 BLAKE 2850 7654 MARTIN 125016 rows selected.SQL>
SQL> with tab as(select '111' id, 'aaa' name, '代理' type from dual union all 2 select '111', 'aaa', '其他' from dual union all 3 select '操作员小计', '', '18' from dual union all 4 select '商户小计', '', '22' from dual union all 5 select '222', 'bbb', '其他' from dual union all 6 select '222', 'bbb', '代理' from dual union all 7 select '操作员小计', '', '99' from dual union all 8 select '商户小计', '', '98' from dual ) 9 select case when id in ('操作员小计','商户小计') then id else decode(rn,1,id,null) end,decode(rn,1,name,null),type from ( 10 select id,name ,type ,row_number()over(partition by id,name order by rownum) rn ,rownum rn1 from tab) 11 order by rn1;
CASEWHENIDIN('操作员小计','商
SQL> with tab as(select '111' id, 'aaa' name, '代理' type from dual union all 2 select '111', 'aaa', '其他' from dual union all 3 select '操作员小计', '', '18' from dual union all 4 select '商户小计', '', '22' from dual union all 5 select '222', 'bbb', '其他' from dual union all 6 select '222', 'bbb', '代理' from dual union all 7 select '操作员小计', '', '99' from dual union all 8 select '商户小计', '', '98' from dual ) 9 select case when id in ('操作员小计','商户小计') then id else decode(rn,1,id,null) end,decode(rn,1,name,null),type from ( 10 select id,name ,type ,row_number()over(partition by id,name order by rownum) rn ,rownum rn1 from tab) 11 order by rn1;
2 select 111, 'aaa', '其他' from dual)
3 select decode(rn,1,id,null),decode(rn,1,name,null),type from (
4 select id,name ,type ,row_number()over(partition by id,name order by type) rn from tab);
DECODE(RN,1,ID,NULL) DECODE(RN,1,NAME,NULL) TYPE
-------------------- ---------------------- ----
111 aaa 代理
其他
SQL>
但是我的结果是统计的
111 aaa 代理111 aaa 其他
操作员小计
商户小计
如果加上这些小计,最后出来的结果就乱了。
不知道这个问题如何能避免
2 操作员小计 1 100.00 0 0.00
3 000000000000000 t123 其他交易 1 123.00 0 0.00
4 操作员小计 1 123.00 0 0.00
5 000000000000000 yqcm 代缴费 2 497.90 0 0.00
6 000000000000000 yqcm 其他交易 2 22.00 0 0.00
7 操作员小计 4 519.90 0 0.00
8 000000000000000 tt234 其他交易 1 122.00 0 0.00
这是我完整的数据。
当我把这个完整的sql用with tab as (sql)这样查询
最后出来的效果就是
1 000000000000000 t11 其他交易 1 100.00 0 0.00
2 000000000000000 t123 其他交易 1 123.00 0 0.00
3 000000000000000 tt234 其他交易 1 122.00 0 0.00
4 000000000000000 yqcm 代缴费 2 497.90 0 0.00
5 其他交易 2 22.00 0 0.00
6 000000000000000 ttt 其他交易 3 233.00 0 0.00
7 操作员小计 1 122.00 0 0.00
8 1 123.00 0 0.00
9 1 100.00 0 0.00
10 4 519.90 0 0.00
11 3 233.00 0 0.00
12 商户小计 10 1097.90 0 0.00
13 总计 10 1097.90 0 0.00
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> break on deptno;
SQL> select deptno,empno,ename,sal from emp order by deptno; DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
10 7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
20 7566 JONES 2975
7902 FORD 3000
7876 ADAMS 1100
7369 SMITH 800
7788 SCOTT 3000
30 7521 WARD 1250
7844 TURNER 1500
7499 ALLEN 1600
7900 JAMES 950
7698 BLAKE 2850
7654 MARTIN 125016 rows selected.SQL>
2 select '111', 'aaa', '其他' from dual union all
3 select '操作员小计', '', '18' from dual union all
4 select '商户小计', '', '22' from dual union all
5 select '222', 'bbb', '其他' from dual union all
6 select '222', 'bbb', '代理' from dual union all
7 select '操作员小计', '', '99' from dual union all
8 select '商户小计', '', '98' from dual )
9 select case when id in ('操作员小计','商户小计') then id else decode(rn,1,id,null) end,decode(rn,1,name,null),type from (
10 select id,name ,type ,row_number()over(partition by id,name order by rownum) rn ,rownum rn1 from tab)
11 order by rn1;
CASEWHENIDIN('操作员小计','商
2 select '111', 'aaa', '其他' from dual union all
3 select '操作员小计', '', '18' from dual union all
4 select '商户小计', '', '22' from dual union all
5 select '222', 'bbb', '其他' from dual union all
6 select '222', 'bbb', '代理' from dual union all
7 select '操作员小计', '', '99' from dual union all
8 select '商户小计', '', '98' from dual )
9 select case when id in ('操作员小计','商户小计') then id else decode(rn,1,id,null) end,decode(rn,1,name,null),type from (
10 select id,name ,type ,row_number()over(partition by id,name order by rownum) rn ,rownum rn1 from tab)
11 order by rn1;
CASEWHENIDIN('操作员小计','商
------------------------------ ---------------------- ----
111 aaa 代理
其他
操作员小计 18
商户小计 22
222 bbb 其他
代理
操作员小计 99
商户小计 98
8 rows selected
奇怪,拷贝出问题