从C表中去字段F1,如果F1=1,则取A表中的F2字段值,如果F1=2,则取B表中的F2字段值,这样一个功能能不能集中到一个SQL语句。
我这样写,好像不行
select c.*,(
case c.f1 when 1 then select f2 from A
case c.f1 when 2 then select f2 from B
) from c where 条件
我这样写,好像不行
select c.*,(
case c.f1 when 1 then select f2 from A
case c.f1 when 2 then select f2 from B
) from c where 条件
with a as(select 'a' f2 from dual),
b as(select 'b' f2 from dual),
c as(select 1 f1 from dual union all select 2 f1 from dual)
select decode(c.f1,1,a.f2,2,b.f2) f from a,b,c;
with a as(select 'a' f2 from dual),
b as(select 'b' f2 from dual),
c as(select 1 f1 from dual union all select 2 f1 from dual)
select c.*,case c.f1 when 1 then a.f2 else b.f2 end f from a,b,c;
F1 F
---------- -
1 a
2 b
select decode(c.F1,1,a.f2,2,b.f2,c.f1) from a,b,c where...
--with table_name as(...)是建一张临时表,测试用的,否则我怎么能跑出结果呢
with a as(select 'a' f2 from dual),
b as(select 'b' f2 from dual),
c as(select 1 f1 from dual union all select 2 f1 from dual)
--下面的是你要的sql语句
select c.*,case c.f1 when 1 then a.f2 else b.f2 end f from a,b,c;
case c.f1 when 1 then '1'
case c.f1 when 2 then '2'
else '3'
end
from c
select a.*, b.bi_flow,
case b.Bi_Flow when 62 then gzk_base.oncounty
case b.Bi_Flow when 63 then gzk_base.oncounty
else gzk_branch.oncounty
end county
from sys_wflog a left join sys_bzinst b on a.wg_code = b.bi_code
where b.bi_flow in (61,62,63) order by b.bi_flow第二个case开始就认不出来了
FROM C,A,B
WHERE C.ID = A.ID
AND C.ID = B.ID
from sys_wflog a left join sys_bzinst b on a.wg_code = b.bi_code
left join
(case b.Bi_Flow when 62 then gzk_base.oncounty
case b.Bi_Flow when 63 then gzk_base.oncounty
else gzk_branch.oncounty
end) c on b.bi_code = c.bi_code
where b.bi_flow in (61,62,63) order by b.bi_flow
不知道这个思路的话改如何改
case b.Bi_Flow when 63 then gzk_base.oncounty
else gzk_branch.oncounty
end)
你这里面有2个case when 怎么可能对,--你要不把上面那段改成这样
decode(b.Bi_Flow,62,gzk_base.oncounty,63,gzk_base.oncounty,gzk_branch.oncounty ) c 还有问题,你left join后面跟的应该是一张表,但你case when或者decode得到的是一个赋值语句,肯定不对
另外,这个SQL怎么有问题 select a.*, b.bi_flow,
decode(b.bi_flow,62,gzk_base.oncounty,b.bi_flow, 63, gzk_base.oncounty,b.bi_flow, 61, gzk_branch.oncounty) county
from sys_wflog a, sys_bzinst b,
where b.bi_flow in (61,62,63) and a.wg_code = b.bi_code and a.wg_code = '[2010]000069' order by b.bi_flow
gzk_branch.oncounty认不出来
decode(b.bi_flow,62,gzk_base.oncounty, 63, gzk_base.oncounty, 61, gzk_branch.oncounty) county
from sys_wflog a, sys_bzinst b,gzk_base,gzk_branch
where b.bi_flow in (61,62,63) and a.wg_code = b.bi_code and a.wg_code = '[2010]000069' order by b.bi_flow 我这样改了之后,出来的条目数又不对了,多了很多了.貌似gzk_base,gzk_branch的关联字段还得加上去,但是这个关联字段很不好加,以为是这两表中取其中一个字段.关联条件是sys_bzinst 表中的bi_code和gzk_base或者gzk_branch中的bi_code关联
你这里b.bi_flow做为where条件,那么表a对表b就不应当是left join,而是inner join。
最后整理得到的SQL如下: select a.*, b.bi_flow, case b.bi_flow when 62 then c.oncounty where 63 then c.oncounty else d.oncounty end
from sys_wflog a
inner join sys_bzinst b
on a.wg_code = b.bi_code and b.bi_flow in (61,62,63)
left join gzk_base c
on b.bi_code = c.bi_code
left join gzk_branch d
on b.bi_code = d.bi_code
order by b.bi_flow;