Question

我有一个疑问,那就是,我试图把某些价值从一个表格中拿走,而一个表格将某个特定的身份识别器放在一边。如果这种价值确实存在,我仍希望回到我所期待的记录中来。这里是我迄今所尝试的。

Select attr.attrval, attr.uidservicepoint, sp.servicepointid 
From bilik.lssrvcptmarketattr attr 
Join bilik.lsmarketattrtype type on attr.uidmarketattrtype = type.uidmarketattrtype AND 
type.attrtype IN ( CAPACITY_REQUIREMENT_KW ) and TO_CHAR( attr.starttime ,  mm/dd/yyyy )in ( 05/01/2011 ) 
Right Outer Join bilik.lsservicepoint sp on attr.uidservicepoint = sp.uidservicepoint
Where sp.servicepointid in ( RGE_R01000051574382 ) Order By sp.servicepointid ASC

例如,Im试图寻找RGE_R01000051574382。如果在表SP中确实如此。在服务点上,我希望它仍然把R01000051574382号区域调查归还给另一个价值I m拉着的标志。通常,当我主持这项工作时,我将在某个时候拿走大约1 000个具体价值。

如果人人都能对此有所了解,将非常感谢。感谢如此之多!

Answer 1

如果我理解正确的话,你只是需要移转<代码>。 WHERE 加入<代码>JOIN条款。

select attr.attrval,
    attr.uidservicepoint,
    sp.servicepointid
from bilik.lssrvcptmarketattr attr
join bilik.lsmarketattrtype type on attr.uidmarketattrtype = type.uidmarketattrtype
    and type.attrtype in ( CAPACITY_REQUIREMENT_KW )
    and TO_CHAR(attr.starttime,  mm/dd/yyyy ) in ( 05/01/2011 )
right outer join bilik.lsservicepoint sp on attr.uidservicepoint = sp.uidservicepoint
    and sp.servicepointid in ( RGE_R01000051574382 )
order by sp.servicepointid

Answer 2

我认为,你再说一遍,你想恢复记录,而服务点一栏则有人居住,但所有其他人都是无效的?

在这种情况下,使用工会。

select   ...your query without order by...
and      sp.servicepointid =  RGE_R010000515743282 
union
select   null, null,  RGE_R010000515743282 
from     dual
where    not exists (select  x  from (...your query without order by...))

举一个完整的例子:

create table test (id number, val varchar2(10));
insert into test (id, val) values (1,  hi );

select   id,
         val
from     test
where    id = 1
union
select   1,
         null
from     dual
where    not exists (select    x 
                     from     test
                     where    id = 1)

友情链接