select sum(to_number(a)),sum(to_number(b)),sum(to_number(c)),sum(to_number(d)) from (select case when Ftype= 'A' then Fname else '' end A, case when Ftype= 'B' then Fname else '' end B, case when Ftype= 'C' then Fname else '' end C, case when Ftype= 'D' then Fname else '' end D, row_number()over(order by Fname) as rn from tab_1 ) tab_2 group by rn-1/4
分组错了,应该是 select sum(to_number(a)),sum(to_number(b)),sum(to_number(c)),sum(to_number(d)) from (select case when Ftype= 'A' then Fname else '' end A, case when Ftype= 'B' then Fname else '' end B, case when Ftype= 'C' then Fname else '' end C, case when Ftype= 'D' then Fname else '' end D, row_number()over(order by Fname) as rn from tab_1 ) tab_2 group by (rn-1)/4
insert into T_test values (1,'aa','aa','A'); insert into T_test values (2,'bb','bb','B'); insert into T_test values (3,'cc','cc','C'); insert into T_test values (4,'dd','dd','D'); insert into T_test values (5,'a2','a2','A'); insert into T_test values (6,'dd','dd','D');commit;select * from T_test;
SQL> select * from T_test; FID FNAME FDEC FTYPE --------------------------------------- ---------- ---------- ----- 1 aa aa A 2 bb bb B 3 cc cc C 4 dd dd D 5 a2 a2 A 6 dd dd D6 行 已选择SQL> SQL> select sum(to_number(a)),sum(to_number(b)),sum(to_number(c)),sum(to_number(d)) 2 from 3 (select 4 case when Ftype= 'A' then 1 else 0 end A, 5 case when Ftype= 'B' then 1 else 0 end B, 6 case when Ftype= 'C' then 1 else 0 end C, 7 case when Ftype= 'D' then 1 else 0 end D, 8 row_number()over(order by Fname) as rn 9 from T_test 10 ) tab_2 11 group by (rn-1)/4 12 /SUM(TO_NUMBER(A)) SUM(TO_NUMBER(B)) SUM(TO_NUMBER(C)) SUM(TO_NUMBER(D)) ----------------- ----------------- ----------------- ----------------- 0 1 0 0 0 0 0 1 0 0 0 1 1 0 0 0 0 0 1 0 1 0 0 06 行 已选择字符用to_number肯定异常.
虽然方法比较笨,但是却没错,呵呵: select (select fname from (select t.*,rownum r from t_test t where ftype='A') t1 where t1.r=a.r) A, (select fname from (select t.*,rownum r from t_test t where ftype='B') t2 where t2.r=a.r) B, (select fname from (select t.*,rownum r from t_test t where ftype='C') t3 where t3.r=a.r) C, (select fname from (select t.*,rownum r from t_test t where ftype='D') t4 where t4.r=a.r) D from (select t.*,rownum r from t_test t) a
我是因为对''进行了to_number出的错.改为: select lpad(sum(to_number(nvl(A,0))),3,'0'),lpad(sum(to_number(nvl(B,0))),3,'0'),lpad(sum(to_number(nvl(C,0))),3,'0'),lpad(sum(to_number(nvl(D,0))),3,'0') from (select case when Ftype= 'A' then Fname else '0' end A, case when Ftype= 'B' then Fname else '0' end B, case when Ftype= 'C' then Fname else '0' end C, case when Ftype= 'D' then Fname else '0' end D, row_number()over(order by Fname) as rn from tab_1 ) tab_2 group by (rn-1)/4 这也要求造的数据中都是数值.
select lpad(sum(to_number(nvl(A,0))),3,'0'),lpad(sum(to_number(nvl(B,0))),3,'0'),lpad(sum(to_number(nvl(C,0))),3,'0'),lpad(sum(to_number(nvl(D,0))),3,'0') from (select case when Ftype= 'A' then Fname else '0' end A, case when Ftype= 'B' then Fname else '0' end B, case when Ftype= 'C' then Fname else '0' end C, case when Ftype= 'D' then Fname else '0' end D, row_number()over(order by Fname) as rn from tab_1 ) tab_2 group by trunc((rn-1)/4)
from
(select
case when Ftype= 'A' then Fname else '' end A,
case when Ftype= 'B' then Fname else '' end B,
case when Ftype= 'C' then Fname else '' end C,
case when Ftype= 'D' then Fname else '' end D,
row_number()over(order by Fname) as rn
from tab_1
) tab_2
group by rn-1/4
select sum(to_number(a)),sum(to_number(b)),sum(to_number(c)),sum(to_number(d))
from
(select
case when Ftype= 'A' then Fname else '' end A,
case when Ftype= 'B' then Fname else '' end B,
case when Ftype= 'C' then Fname else '' end C,
case when Ftype= 'D' then Fname else '' end D,
row_number()over(order by Fname) as rn
from tab_1
) tab_2
group by (rn-1)/4
按你的SQL运行是出现"ORA-01722: 无效数字"以下是测试用表的脚本,请大家帮忙试一下:CREATE TABLE T_test
(
fid INTEGER,
fname VARCHAR2(10),
fdec VARCHAR2(10),
ftype CHAR(1)
)LOGGING
NOCACHE
NOPARALLEL
NOMONITORING;
ALTER TABLE T_test ADD (
PRIMARY KEY
(fid));
insert into T_test values (1,'aa','aa','A');
insert into T_test values (2,'bb','bb','B');
insert into T_test values (3,'cc','cc','C');
insert into T_test values (4,'dd','dd','D');
insert into T_test values (5,'a2','a2','A');
insert into T_test values (6,'dd','dd','D');commit;select * from T_test;
--------------------------------------- ---------- ---------- -----
1 aa aa A
2 bb bb B
3 cc cc C
4 dd dd D
5 a2 a2 A
6 dd dd D6 行 已选择SQL>
SQL> select sum(to_number(a)),sum(to_number(b)),sum(to_number(c)),sum(to_number(d))
2 from
3 (select
4 case when Ftype= 'A' then 1 else 0 end A,
5 case when Ftype= 'B' then 1 else 0 end B,
6 case when Ftype= 'C' then 1 else 0 end C,
7 case when Ftype= 'D' then 1 else 0 end D,
8 row_number()over(order by Fname) as rn
9 from T_test
10 ) tab_2
11 group by (rn-1)/4
12 /SUM(TO_NUMBER(A)) SUM(TO_NUMBER(B)) SUM(TO_NUMBER(C)) SUM(TO_NUMBER(D))
----------------- ----------------- ----------------- -----------------
0 1 0 0
0 0 0 1
0 0 0 1
1 0 0 0
0 0 1 0
1 0 0 06 行 已选择字符用to_number肯定异常.
select
(select fname from (select t.*,rownum r from t_test t where ftype='A') t1 where t1.r=a.r) A,
(select fname from (select t.*,rownum r from t_test t where ftype='B') t2 where t2.r=a.r) B,
(select fname from (select t.*,rownum r from t_test t where ftype='C') t3 where t3.r=a.r) C,
(select fname from (select t.*,rownum r from t_test t where ftype='D') t4 where t4.r=a.r) D
from (select t.*,rownum r from t_test t) a
select lpad(sum(to_number(nvl(A,0))),3,'0'),lpad(sum(to_number(nvl(B,0))),3,'0'),lpad(sum(to_number(nvl(C,0))),3,'0'),lpad(sum(to_number(nvl(D,0))),3,'0')
from
(select
case when Ftype= 'A' then Fname else '0' end A,
case when Ftype= 'B' then Fname else '0' end B,
case when Ftype= 'C' then Fname else '0' end C,
case when Ftype= 'D' then Fname else '0' end D,
row_number()over(order by Fname) as rn
from tab_1
) tab_2
group by (rn-1)/4
这也要求造的数据中都是数值.
from
(select
case when Ftype= 'A' then Fname else '0' end A,
case when Ftype= 'B' then Fname else '0' end B,
case when Ftype= 'C' then Fname else '0' end C,
case when Ftype= 'D' then Fname else '0' end D,
row_number()over(order by Fname) as rn
from tab_1
) tab_2
group by trunc((rn-1)/4)