个sql语句的有关问题

弱弱的问个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

[其他解释]
你百度 oracle merge into 用法就可以了
[其他解释]
没学会。。

引用:

你百度 oracle merge into 用法就可以了

[其他解释]
都是牛人，都没告诉你怎么插入(字段怎么对应)，N多SQL语句就出来了。
[其他解释]

引用:

都是牛人，都没告诉你怎么插入(字段怎么对应)，N多SQL语句就出来了。

呵呵，表二里面的数据不就是想要的结果么。。

个sql语句的有关问题

热点推荐