时间戳

首页 > 解决方案 > SQL Oracle - 如果不满足某个条件，则将记录插入同一个表中

sql - SQL Oracle - 如果不满足某个条件，则将记录插入同一个表中

问题描述

我有两个表：rate_card和rate配置如下：

create table rate_card (RateCard varchar(13), Currency varchar(3));    

insert into rate_card values('2DaysUSD','USD');
insert into rate_card values('3DaysUSD','USD'); 
insert into rate_card values('2DaysJPY','JPY');`table`


create table rate (Currency varchar(3),Rate varchar(19));

insert  into  rate values ('USD','0.6');
insert  into  rate values('JPY','0.4');

我想得到如下输出：

输出

当我运行查询时将显示一次黄色，因为Ratecard = '1DaysUSD'它不可用。

对于其他人，我只是用来Union显示三个记录“Spot”、“Today”、“Tomorrow”，如下所示：

select c.RateCard, c.Currency, 'SPOT' from rate_card c, rate r where c.Currency = r.Currency
union
select c.RateCard, c.Currency, 'Today' from rate_card c, rate r where c.Currency = r.Currency
union
select c.RateCard, c.Currency, 'Tomorrow' from rate_card c, rate r where c.Currency = r.Currency;

你能告诉我正确的方法吗？

标签： sqloracleplsql

解决方案

您可以使用多个子查询来实现这一点connect by，如下所示：

Select RateCard, 
Currency, 
rate, 
Case lvl when 1 then 'Spot'
         When 2 then 'Today'
         Else 'Tomorrow' 
end as type
From
  (select c.RateCard, 
          c.Currency, 
          r.rate  
     from (Select ratecard, 
                  currency 
             from rate_card
           Union
           Select &&your_inpur_rate_card, 
        substr(&&your_inpur_rate_card, length(&&your_inpur_rate_card - 2)) 
          from dual) c 
     join rate r 
     on c.Currency = r.Currency)
 Join
 (Select level as lvl 
    from dual 
  connect by level <= 3)
 on 1=1;

干杯！！

推荐阅读