select CP.provider as f1,PC.person as f2,tb_system.system_name as f3,PC.name as f4,PC.units as f5,t6.value_s as f6,
PC.count_s as f7,(case when instr(tb_cp.sourceids,PC.sourcecode) > 0 then tb_cp.infoname else null end)as f8,
t6.kpi_id as f10
from product_om PC,p_provider CP,p_system tb_system,p_info tb_cp,
(select t4.id, t4.cn_name, t5.value_s,t5.no,t5.time from p_value t5, kp_info t4 where t5.no = t4.no and t5.level = 4
and t5.time = 20110901) t6
where PC.providerid=CP.id(+)
and PC.system=tb_system.system_id
and PC.id = t6.id(+)
and CP.id = CP.id
and PC.person = PC.person
and PC.units <>'条'
and PC.count_s like '%月%'
order by PC.id
上面这段程序主要是取出product_om中产品的信息。里面有对应部门,系统,销售人员的id,找到相关部门名,系统名,人名后写到对应的产品后。现在报表上要加1个和产品对应的产品信息的表(即p_info tb_cp)。product_om里面有个字段sourcecode,是产品的信息id,值是{cs121}{es225}。p_info tb_cp里有个字段是sourceids,值是{cs121,cs234,es223}{es225,es226}我现在想用(case when instr(tb_cp.sourceids,PC.sourcecode) > 0 then tb_cp.infoname else null end)的方法找到p_info.sourceids里包含product_om.sourcecode的记录行,然后把p_info.infoname写到相关的产品后我也想用left join on来写,但是已经有(+),再加不好加了
PC.count_s as f7,(case when instr(tb_cp.sourceids,PC.sourcecode) > 0 then tb_cp.infoname else null end)as f8,
t6.kpi_id as f10
from product_om PC,p_provider CP,p_system tb_system,p_info tb_cp,
(select t4.id, t4.cn_name, t5.value_s,t5.no,t5.time from p_value t5, kp_info t4 where t5.no = t4.no and t5.level = 4
and t5.time = 20110901) t6
where PC.providerid=CP.id(+)
and PC.system=tb_system.system_id
and PC.id = t6.id(+)
and CP.id = CP.id
and PC.person = PC.person
and PC.units <>'条'
and PC.count_s like '%月%'
order by PC.id
上面这段程序主要是取出product_om中产品的信息。里面有对应部门,系统,销售人员的id,找到相关部门名,系统名,人名后写到对应的产品后。现在报表上要加1个和产品对应的产品信息的表(即p_info tb_cp)。product_om里面有个字段sourcecode,是产品的信息id,值是{cs121}{es225}。p_info tb_cp里有个字段是sourceids,值是{cs121,cs234,es223}{es225,es226}我现在想用(case when instr(tb_cp.sourceids,PC.sourcecode) > 0 then tb_cp.infoname else null end)的方法找到p_info.sourceids里包含product_om.sourcecode的记录行,然后把p_info.infoname写到相关的产品后我也想用left join on来写,但是已经有(+),再加不好加了
我现在就是不知道这段,怎么加到有(+)的sql里。所以用的case when,但是查出了几万条数据
PC.person as f2,
tb_system.system_name as f3,
PC.name as f4,
PC.units as f5,
t6.value_s as f6,
PC.count_s as f7,
--(case when instr(tb_cp.sourceids,PC.sourcecode) > 0 then tb_cp.infoname else null end)as f8,
(SELECT infoname FROM p_info WHERE instr(p_info.sourceids,PC.sourcecode) > 0 AND ROWNUM = 1)as f8,
t6.kpi_id as f10
from product_om PC,
p_provider CP,
p_system tb_system,
--p_info tb_cp,
(select t4.id, t4.cn_name, t5.value_s,t5.no,t5.time
from p_value t5, kp_info t4
where t5.no = t4.no and t5.level = 4 and t5.time = 20110901
) t6
where PC.providerid=CP.id(+)
and PC.system=tb_system.system_id
and PC.id = t6.id(+)
and CP.id = CP.id
and PC.person = PC.person
and PC.units <>'条'
and PC.count_s like '%月%'
order by PC.id上面的SQL有一个可能会出现BUG的问题,那就是当product_om表中的一条记录,通过instr()方式去p_info表中查找对应记录时会不会出现有多条记录符合该需求的情况?如果多条记录符合该需求时,应当取哪条记录做为连接的值呢?对此,楼主还没有给出答案,在这里我采用了ROWNUM = 1方式来取值,也就是存在多条对应的记录的情况下,我取第1条记录做为连接的值。
and PC.person = PC.person这2个条件有什么意义
PC.person as f2,
(select system_name from p_system where system_id=PC.system) as f3,
PC.name as f4,
PC.units as f5,
t6.value_s as f6,
PC.count_s as f7,
(select infoname from p_info where instr(p_info.sourceids,PC.sourcecode) > 0 AND ROWNUM = 1)as f8,
t6.kpi_id as f10
from product_om PC left join p_provider CP on PC.providerid=CP.id
left join
(select t4.id, t4.cn_name, t5.value_s,t5.no,t5.time from p_value t5, kp_info t4 where t5.no = t4.no and t5.level = 4
and t5.time = 20110901) t6 on PC.id = t6.id
where and CP.id = CP.id
and PC.person = PC.person
and PC.units <>'条'
and PC.count_s like '%月%'
order by PC.id
select CP.provider as f1,
PC.person as f2,
(select system_name from p_system where system_id=PC.system) as f3,
PC.name as f4,
PC.units as f5,
t6.value_s as f6,
PC.count_s as f7,
(select max(infoname) from p_info where instr(p_info.sourceids,PC.sourcecode)> 0)as f8,
t6.kpi_id as f10
from product_om PC left join p_provider CP on PC.providerid=CP.id
left join
(select t4.id, t4.cn_name, t5.value_s,t5.no,t5.time from p_value t5, kp_info t4 where t5.no = t4.no and t5.level = 4
and t5.time = 20110901) t6 on PC.id = t6.id
where CP.id = CP.id
and PC.person = PC.person
and PC.units <>'条'
and PC.count_s like '%月%'
order by PC.id
and PC.person = PC.person
是因为传参的时候,对“全部”的情况不做判断,这和现在讨论的问题无关,可以不用考虑。谢谢各位的回复,还有其它办法吗?
select CP.provider as f1,
PC.person as f2,
(select system_name from p_system where system_id=PC.system) as f3,
PC.name as f4,
PC.units as f5,
t6.value_s as f6,
PC.count_s as f7,
(select infoname from p_info where instr(p_info.sourceids,PC.sourcecode) > 0 AND ROWNUM = 1)as f8,
t6.kpi_id as f10
from product_om PC left join p_provider CP on PC.providerid=CP.id
left join
(select t4.id, t4.cn_name, t5.value_s,t5.no,t5.time from p_value t5, kp_info t4 where t5.no = t4.no and t5.level = 4
and t5.time = 20110901) t6 on PC.id = t6.id
where and CP.id = CP.id
and PC.person = PC.person
and PC.units <>'条'
and PC.count_s like '%月%'
order by PC.id
PC.person as f2,
(select system_name from p_system where system_id=PC.system) as f3,
PC.name as f4,
PC.units as f5,
t6.value_s as f6,
PC.count_s as f7,
(select infoname from p_info where instr(p_info.sourceids,PC.sourcecode) > 0 AND ROWNUM = 1)as f8,
t6.kpi_id as f10
from product_om PC left join p_provider CP on PC.providerid=CP.id
left join
(select t4.id, t4.cn_name, t5.value_s,t5.no,t5.time from p_value t5, kp_info t4 where t5.no = t4.no and t5.level = 4
and t5.time = 20110901) t6 on PC.id = t6.id
where
and PC.units <>'条'
and PC.count_s like '%月%'
order by PC.id