弱弱的问个sql语句的问题
本帖最后由 fulima007 于 2012-11-23 11:44:17 编辑 两个表
表1：主键是(id, name, path)
id name path value
1 fu1 /home 0
2 fu2 /home 1
3 fu3 /home 2
3 fu3 /test 0
1 fu1 /test 4
其中value=0 代表成功，value！= 0代表失败
表2：表的主键是(id, name)同样是外键，
id name succ fail
1 fu1 1 1
2 fu2 0 1
3 fu3 1 1
写一个sql语句，通过查询表1并将结果插入到表2中。
[最优解释]
insert into tb2
select
id ,
name ,
sum(decode(value,0,1,0)) succ ,
sum(decode(value,0,0,1)) fail
from tb1
group by id ,name
order by id ,name
merge into tb2
using tb1
on(tb1.id=tb2.id and tb1.name=tb2.name)
when matched then
update tb2
set (succ,fail)=(select a.succ,a.fail from(
select id , name , sum(decode(value,0,1,0)) succ ,
sum(decode(value,0,0,1)) fail
from tb1
group by id ,name
order by id ,name)a);
when not matched then
insert into tb2
select id ,name ,sum(decode(value,0,1,0)) succ ,sum(decode(value,0,0,1)) fail
from tb1
group by id ,name
order by id ,name;
merge into tb2
using tb1
on(tb1.id=tb2.id and tb1.name=tb2.name)
when matched then
update tb2
set (succ,fail)=(select a.succ,a.fail from(
select id , name , sum(decode(value,0,1,0)) succ ,
sum(decode(value,0,0,1)) fail
from tb1
group by id ,name
order by id ,name)a)
when not matched then
insert into tb2
select id ,name ,sum(decode(value,0,1,0)) succ ,sum(decode(value,0,0,1)) fail
from tb1
group by id ,name
order by id ,name;
WITH tb AS (
SELECT 1 ID,'fu1' NAME, 0 VALUE FROM dual UNION ALL
SELECT 2 ID,'fu2' NAME, 1 VALUE FROM dual UNION ALL
SELECT 3 ID,'fu3' NAME, 2 VALUE FROM dual UNION ALL
SELECT 3 ID,'fu3' NAME, 0 VALUE FROM dual UNION ALL
SELECT 1 ID,'fu1' NAME, 4 VALUE FROM dual )
INSERT INTO 表2
SELECT ID,NAME,sum(decode(suc,0,n,0)),sum(decode(suc,1,n,0)) FROM (
SELECT ID,NAME,decode(VALUE,0,0,1) suc,COUNT(1) n FROM tb GROUP BY ID,NAME,decode(VALUE,0,0,1))
GROUP BY ID,NAME
