弱弱的问个sql语句的问题

Question

本帖最后由 fulima007 于 2012-11-23 11:44:17 编辑

Answer 1

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

Answer 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

Answer 3

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;

Answer 4

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;上面的多了一个分号 

Answer 5

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

Answer 6

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