SELECT DECODE(u.chargetype,'A',TARIFF_A,'B',TARIFF_B,'C',TARIFF_C,'D',TARIFF_D) FROM USERINFO u
TARIFF_A等是表,根据chargetype从不同的表查询
T 的值= TARIFF_A,TARIFF_B,TARIFF_C,TARIFF_D 中的一个 t.TOLL ? 想要什么/?
你的表不固定,sql是写不出来的
如果是存储过程里 就用动态SQL吧!! 你那样单写SQL应该是不行的!
试试这样可不可以,不过性能效率肯定不怎么好的!要看你具体表。 假设你TARIFF_A,TARIFF_B,TARIFF_B 表结构一样或类似SELECT t.TOLL FROM USERINFO u, (SELECT 'A' chargetype,a.* from TARIFF_A a UNION ALL SELECT 'B' chargetype,a.* from TARIFF_B a UNION ALL SELECT 'C' chargetype,a.* from TARIFF_C a UNION ALL SELECT 'D' chargetype,a.* from TARIFF_D a ) t where u.chargetype=t.chargetype
不符合逻辑阿,如果表USERINFO 中的字段值有:A ,B 2条纪录,那么只有先把,TARIFF_A,TARIFF_B,TARIFF_C,TARIFF_D求并集,这样的查询真是不值得。 select decode(u.chargetype,'A',t.a,'B',t.b,'C',t.c,'D',t.d) col, from (select TARIFF_A.toll a,TARIFF_B.toll b,TARIFF_C.toll c,TARIFF_D.toll d from TARIFF_A,TARIFF_B,TARIFF_C,TARIFF_D) t,userinfo u
最终使用Case解决了,v_ChargeType变量中得到Type CASE v_ChargeType WHEN 'A' THEN SELECT TOLL INTO v_out_Charge FROM TARIFF_A WHERE ONRAMPID=v_in_OnRampID AND OFFRAMPID=v_in_OffRampID; WHEN 'B' THEN SELECT TOLL INTO v_out_Charge FROM TARIFF_B WHERE ONRAMPID=v_in_OnRampID AND OFFRAMPID=v_in_OffRampID; WHEN 'C' THEN SELECT TOLL INTO v_out_Charge FROM TARIFF_C WHERE ONRAMPID=v_in_OnRampID AND OFFRAMPID=v_in_OffRampID; WHEN 'D' THEN SELECT TOLL INTO v_out_Charge FROM TARIFF_D WHERE ONRAMPID=v_in_OnRampID AND OFFRAMPID=v_in_OffRampID; ELSE v_out_Charge:=0; END CASE;
select decode('d','a',1,'b',2,'c',3) from dual
SELECT t.TOLL FROM USERINFO u, (select DECODE(u.chargetype,'A',TARIFF_A,'B',TARIFF_B,'C',TARIFF_C,'D',TARIFF_D) from USERINFO u) t
FROM USERINFO u
t.TOLL ?
想要什么/?
你那样单写SQL应该是不行的!
试试这样可不可以,不过性能效率肯定不怎么好的!要看你具体表。
假设你TARIFF_A,TARIFF_B,TARIFF_B 表结构一样或类似SELECT t.TOLL FROM USERINFO u,
(SELECT 'A' chargetype,a.* from TARIFF_A a
UNION ALL
SELECT 'B' chargetype,a.* from TARIFF_B a
UNION ALL
SELECT 'C' chargetype,a.* from TARIFF_C a
UNION ALL
SELECT 'D' chargetype,a.* from TARIFF_D a
) t
where u.chargetype=t.chargetype
select decode(u.chargetype,'A',t.a,'B',t.b,'C',t.c,'D',t.d) col, from
(select TARIFF_A.toll a,TARIFF_B.toll b,TARIFF_C.toll c,TARIFF_D.toll d from TARIFF_A,TARIFF_B,TARIFF_C,TARIFF_D) t,userinfo u
CASE v_ChargeType
WHEN 'A' THEN
SELECT TOLL INTO v_out_Charge
FROM TARIFF_A
WHERE ONRAMPID=v_in_OnRampID AND OFFRAMPID=v_in_OffRampID;
WHEN 'B' THEN
SELECT TOLL INTO v_out_Charge
FROM TARIFF_B
WHERE ONRAMPID=v_in_OnRampID AND OFFRAMPID=v_in_OffRampID;
WHEN 'C' THEN
SELECT TOLL INTO v_out_Charge
FROM TARIFF_C
WHERE ONRAMPID=v_in_OnRampID AND OFFRAMPID=v_in_OffRampID;
WHEN 'D' THEN
SELECT TOLL INTO v_out_Charge
FROM TARIFF_D
WHERE ONRAMPID=v_in_OnRampID AND OFFRAMPID=v_in_OffRampID;
ELSE v_out_Charge:=0;
END CASE;
FROM USERINFO u,
(select DECODE(u.chargetype,'A',TARIFF_A,'B',TARIFF_B,'C',TARIFF_C,'D',TARIFF_D) from USERINFO u) t