表1(ID,CODE,GROUP,VALUE)ID CODE GROUP VALUE
1 A A1 2
2 A A2 1
3 A A3 2表2 (ID,CODE,CODE_TO,VALUE) ID和CODE与表1关联ID CODE CODE_TO VALUE
1 A B 3
3 A C 1输出:
CODE VALUE
A 5 --表1CODE = A的合计
B 3 --表2CODE_TO = B的合计
C 1 --表2CODE_TO = C的合计各位大侠帮帮忙。。要求用一条SQL语句实现。
1 A A1 2
2 A A2 1
3 A A3 2表2 (ID,CODE,CODE_TO,VALUE) ID和CODE与表1关联ID CODE CODE_TO VALUE
1 A B 3
3 A C 1输出:
CODE VALUE
A 5 --表1CODE = A的合计
B 3 --表2CODE_TO = B的合计
C 1 --表2CODE_TO = C的合计各位大侠帮帮忙。。要求用一条SQL语句实现。
select code,sum(value) from 表1
union all
select code_to,value from 表2
select code,sum(value) from 表1 where code = 'a' group by code
union all
select code_to,sum(value) from 表2 where code in ('b','c') group by code
select code, value from
(select code,sum(value) value,code ord from tab1 group by code
union all
select code_to,sum(value),code from tab2 t
where exist (select * from tab2 where t.id=id and t.code=code)
group by code_to
) t
order by ord,code;
运行上面语句,
select code_to,sum(value),code from tab2 t
where exist (select * from tab2 where t.id=id and t.code=code)
group by code_to
这一段中
报错:ORA-00936: missing expression另外code 处会不会报:
ORA-00979: not a GROUP BY expression ?--附:表的创建语句如下:
CREATE TABLE tab1(
id VARCHAR2(2),
code VARCHAR(2),
g_group VARCHAR(4),
value NUMBER(4),
PRIMARY KEY(id, code)
)
/CREATE TABLE tab2(
id VARCHAR2(2),
code VARCHAR(2),
code_to VARCHAR(2),
value NUMBER(4),
FOREIGN KEY (id,code) REFERENCES tab1(id, code)
)
/
(select 1 ID,'A' CODE,'A1' grou,2 VALUE from dual union all
select 2,'A','A2',1 from dual union all
select 3,'A','A3',2 from dual ),
tb2 as(
select 1 ID,'A' CODE,'B' CODE_TO,3 VALUE from dual union all
select 3,'A','C',1 from dual)
select code,sum(value) from tb1 group by code
union
select code_to,sum(value) from tb2 code_to group by code_to--or
select nvl(b.code_to,a.code) "code",
nvl(sum(b.value) over(partition by b.code,b.code_to),
sum(a.value) over(partition by a.code)) total
from tb1 a full outer join tb2 b
on a.code=b.code and a.id=b.id
--结果CODE SUM(VALUE)
---- ----------
A 5
B 3
C 1
--这位兄弟写快了 漏了 补上select code, value from
(select code,sum(value) value,code ord from tab1 group by code
union all
select code_to,sum(value),code from tab2 t
where exists(少了s) (select * from tab1(不是tab2) where t.id=id and t.code=code)
group by code_to,code(少了)) t
order by ord,code;