表T中 有如下字段
a,b,d
其对应值如下:
01,南大, 24
01,南大,12
02,北大,10
02,北大,2我现在想要这样的结果:
01,南大, 24
01,南大,12
01,南大,36(12+24)02,北大,10
02,北大,202,北大,12(10+2)
请问如何实现啊? 在线等,谢谢!!!
a,b,d
其对应值如下:
01,南大, 24
01,南大,12
02,北大,10
02,北大,2我现在想要这样的结果:
01,南大, 24
01,南大,12
01,南大,36(12+24)02,北大,10
02,北大,202,北大,12(10+2)
请问如何实现啊? 在线等,谢谢!!!
[SYS@myorcl] SQL>WITH t1 AS(
2 SELECT '01' a,'南大' b, 24 c FROM dual UNION ALL
3 SELECT '01' a,'南大' b, 12 c FROM dual UNION ALL
4 SELECT '02' a,'北大' b, 10 c FROM dual UNION ALL
5 SELECT '02' a,'北大' b, 2 c FROM dual
6 )SELECT a,b,c FROM t1
7 UNION
8 SELECT a,b,SUM(c) FROM t1 GROUP BY a,b
9 ;A B C
-- ---- ----------
01 南大 12
01 南大 24
01 南大 36
02 北大 2
02 北大 10
02 北大 12已选择6行。
insert into t(a,b,d) values('01','南大',12);
insert into t(a,b,d) values('02','北大',10);
insert into t(a,b,d) values('02','北大',2);col a for a10;
col b for a10;
col d for a15;
select * from (
select t1.a, t1.b, to_char(t1.d) as d
from t t1
union all
select t2.a, t2.b, regexp_replace(wm_concat(to_char(t2.d)),',','+') as d
from t t2
group by t2.a, t2.b ) tt
order by a, b, length(d);
SQL> insert into t(a,b,d) values('01','南大',24);已创建 1 行。SQL> insert into t(a,b,d) values('01','南大',12);已创建 1 行。SQL> insert into t(a,b,d) values('02','北大',10);已创建 1 行。SQL> insert into t(a,b,d) values('02','北大',2);已创建 1 行。SQL>
SQL> col a for a10;
SQL> col b for a10;
SQL> col d for a15;
SQL> select * from (
2 select t1.a, t1.b, to_char(t1.d) as d
3 from t t1
4 union all
5 select t2.a, t2.b, to_char(sum(d))||' ('||regexp_replace(wm_concat(to_char(t2.d)),',','+')||')' as d
6 from t t2
7 group by t2.a, t2.b ) tt
8 order by a, b, length(d), d;A B D
---------- ---------- ---------------
01 南大 12
01 南大 24
01 南大 36 (24+12)
02 北大 2
02 北大 10
02 北大 12 (10+2)已选择6行。
-- 如果加号(+)中的数字需要按从小到大排列的话,可以这样:
col a for a10;
col b for a10;
col d for a15;
select * from (
select t1.a, t1.b, to_char(t1.d) as d
from t t1
union all
select t2.a, t2.b, to_char(sum(d))||' ('||regexp_replace(wm_concat(to_char(t2.d)),',','+')||')' as d
from (select a, b, d from t t3 order by a, b, d) t2
group by t2.a, t2.b ) tt
order by a, b, length(d), d;
(select zhh,sum(nvl(a.jbfjc,0)+nvl(a.fjc1,0)+nvl(a.fjc2,0)+nvl(a.fjc3,0)+nvl(a.pjc1,0)+nvl(a.pjc2,0)+nvl(a.pjc3,0)+nvl(a.gjc1,0)+nvl(a.gjc2,0)+nvl(a.gjc3,0) ) sum_fee
from e_calc_inv a
where a.ny>=to_char(add_months(trunc(sysdate,'MM'), -12),'YYYYMM')
and a.ny<to_char(add_months(trunc(sysdate,'MM'), 0),'YYYYMM')
and zhh='0151592248'
group by zhh
)select a.zhh,a.hm, a.ny,cos,
nvl(a.jbfjc,0)+nvl(a.fjc1,0)+nvl(a.fjc2,0)+nvl(a.fjc3,0)+nvl(a.pjc1,0)+nvl(a.pjc2,0)+nvl(a.pjc3,0)+nvl(a.gjc1,0)+nvl(a.gjc2,0)+nvl(a.gjc3,0) fee ,b.sum_fee from e_calc_inv a ,temp b
where a.ny>=to_char(add_months(trunc(sysdate,'MM'), -12),'YYYYMM')
and a.ny<to_char(add_months(trunc(sysdate,'MM'), 0),'YYYYMM')
and a.zhh='0151592248'
and b.zhh=a.zhh
这样的该怎么修改啊?谢谢啊!!!
SELECT '01','南大',24 FROM dual UNION
SELECT '01','南大',12 FROM dual UNION
SELECT '02','北大',10 FROM dual UNION
SELECT '02','北大',2 FROM dual ;
COMMIT;CREATE TABLE t_abd AS
SELECT a,b,d,Row_number()over(PARTITION BY a,b ORDER BY d) ord FROM abd;
/DECLARE p_sql VARCHAR2(1000);p_i INTEGER;
BEGIN
SELECT COUNT(DISTINCT Ord) INTO p_i FROM t_abd;
FOR i IN 1..p_i
LOOP
p_sql:= p_sql||','||'MAX(CASE WHEN ord ='||i||' THEN d ELSE NULL END) '||'c'||i;
p_i := p_i+1;
END LOOP;
p_sql := 'CREATE TABLE mytable AS SELECT a,b '||p_sql||',SUM(D) s FROM t_abd GROUP BY a,b';
DBMS_OUTPUT.put_line(p_sql);
EXECUTE IMMEDIATE p_sql;
END;
/
SELECT a,b,TO_CHAR(d) d FROM abd UNION
SELECT a,b,S||'('||C1||'+'||C2||')' FROM mytable
ORDER BY d;
/DROP TABLE abd;
DROP TABLE t_abc;
DROP TABLE mytable;
WITH T1 AS(
SELECT A.ZHH,
A.HM,
A.NY,
COS,
NVL(A.JBFJC, 0) + NVL(A.FJC1, 0) + NVL(A.FJC2, 0) + NVL(A.FJC3, 0) +
NVL(A.PJC1, 0) + NVL(A.PJC2, 0) + NVL(A.PJC3, 0) + NVL(A.GJC1, 0) +
NVL(A.GJC2, 0) + NVL(A.GJC3, 0) FEE
FROM E_CALC_INV A
WHERE A.NY >= TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12), 'YYYYMM')
AND A.NY < TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 0), 'YYYYMM')
AND A.ZHH = '0151592248'
)SELECT ZHH, HM, NY, COS, FEE
FROM T1
UNION
SELECT ZHH, '', '', '', SUM(FEE)
FROM T1 GROUP BY ZHH;
ora-01790:expression must hava same datatype as corresponding expression