oracle游标求解

[bufan2162]

输入日志表TICKET_IN_MSG


字段名 字段类型 字段描述 字段约束
TICKET_IN_ID NUNBER(9) 日志标识 PRIMARY
MODULE_ID NUMBER(3) 模块号 NOT NULL
BUSINESS_ID NUMBER(3) 业务号 NOT NULL
TICKET_COUNT NUMBER(9) 条数 NOT NULL
STATE_DATE DATE 处理日期 NOT NULL

输出日志表 TICKET_OUT_MSG

字段名 字段类型 字段描述 字段约束
TICKET_OUT_ID NUNBER(9) 日志标识 PRIMARY
MODULE_ID NUMBER(3)　　 模块号 NOT NULL
BUSINESS_ID NUMBER(3) 业务号 NOT NULL
TICKET_TYPE NUMBER(3) 条类型(1：正确；2：错误) NOT NULL
TICKET_COUNT NUMBER(9)条数 NOT NULL
STATE_DATE DATE 处理日期 NOT NULL

MODULE模块表
MODULEID , MODULENAME
BUSINESS业务表
BUSINESSID,BUSINESSNAME

得出下列并插入到另一张新表：
模块名称 业务名称 输入条数 输出正确条数 输出错误条数

求解法！

[bufan2162]

1楼你母亲的。

[小灰狼W]

select c.modulename,
d.businessname,
count(1)c_in,
count(decode(b.ticket_type,1,1))c_right,
count(decode(b.ticket_type,2,1))c_wrong
from TICKET_IN_MSG a left join TICKET_OUT_MSG b
on a.module_id=b.module_id
and a.business_id=b.business_id
join MODULE c on a.module_id=c.moduleid
join business d on a.business_id=c.businessid
group by a.module_id,c.modulename,a.business_id, d.businessname

[maitianhust]

UP,狂浪出马，一个顶两个，相信他。
[Quote=引用 3 楼 wildwave 的回复:]
select c.modulename,
  d.businessname,
  count(1)c_in,
  count(decode(b.ticket_type,1,1))c_right,
  count(decode(b.ticket_type,2,1))c_wrong
from TICKET_IN_MSG a left join TICKET_OUT_MSG b
  on a.module_id=b.module_id
    and a.business_id=b.business_id
join MODULE c on a.module_id=c.moduleid
join business d on a.business_id=c.businessid
group by a.module_id,c.modulename,a.business_id, d.businessname
[/Quote]

[bufan2162]

不对

[sharpidd]

怪怪得,SQL可以执行吗?

[suiziguo]

insert into your_new_table
select c.MODULENAME,d.BUSINESSNAME,nvl(sum(a.TICKET_COUNT),0),nvl(sum(b1.TICKET_COUNT),0),nvl(sum(b2.TICKET_COUNT),0) from TICKET_IN_MSG a,TICKET_OUT_MSG b1,TICKET_OUT_MSG b2,MODULE c,BUSINESS d where a.MODULE_ID(+)=c.MODULEID and b1.MODULE_ID(+)=c.MODULEID and b2.MODULE_ID(+)=c.MODULEID and a.BUSINESS_ID(+)=d.BUSINESSID and b1.BUSINESS_ID(+)=d.BUSINESSID and b2.BUSINESS_ID(+)=d.BUSINESSID and b1.TICKET_TYPE=1 and b2.TICKET_TYPE=2 group by c.MODULENAME,d.BUSINESSNAME;