我有两个表,我想在第一个表上增加查询的输出第二个表上的另一个查询的值。
我可以使用下面的第二个查询从第二个表获得我想要的值。这个查询可以返回多行。
select ip_country from table2
where
account_id = 'customer1' and
created_on >= to_timestamp('15-NOV-2013','dd-MON-yyyy')
order by created_on desc;
输出: us
(null)
us
(null)
我只对最新的行感兴趣,所以我使用这样的查询
select ip_country from (select ip_country from table2
where
account_id = 'customer1' and
created_on >= to_timestamp('15-NOV-2013','dd-MON-yyyy')
order by created_on desc) where rownum = 1;
输出:us
我想把这个扩展成这样的查询:
select
txnid, account_id, result
from table1
where
table1.txnid = 101
输出:101, customer1, PASS
我想写一个像
这样的查询select
txnid, account_id, result, ip_country = (select ip_country from (select ip_country from table2
where
account_id = 'customer1' and
created_on >= to_timestamp('15-NOV-2013','dd-MON-yyyy')
order by created_on desc) where rownum = 1;)
from table1, table2
where
table1.txnid = 101;
这当然在语法上是错误的,但希望这能传达意思。我的问题是如何写这个查询。
所期望的输出:101, customer1, PASS, us
我尝试使用连接,但没有任何运气。我在这里省略了一些与查询无关的其他列。我正在做一个Oracle数据库。请随意更改标题,我不知道如何更好地表达
我想这会达到你的目的:
select txnid, account_id, result,
(select max(ip_country) keep (dense_rank first order by created_on desc)
from table2
where account_id = 'customer1' and
created_on >= to_timestamp('15-NOV-2013','dd-MON-yyyy')
) as ip_country
from table1
where table1.txnid = 101;
然而,我认为你的查询有两个简单的问题。首先,子查询中有一个分号,其次,外部查询中有一个不必要的连接。上面通过使用oracle的keep
/first
功能消除了一层子查询。
如果需要相同的account_id:
select t1.txnid, t1.account_id, t1.result, t2max.ip_country
from table1 t1,
(select t2.ip_country, t2.account_id from table2 t2
where t2.account_id = 'customer1' and
t2.created_on >= to_timestamp('15-NOV-2013','dd-MON-yyyy')
order by t2.created_on desc limit 1) t2max
where t2max.account_id = t1.account_id
group by t1.txnid, t1.account_id, t1.result;
或者table1和table2中的account_id不相同:
select t1.txnid, t1.account_id, t1.result, t2max.ip_country
from table1 t1,
(select t2.ip_country from table2 t2
where t2.account_id = 'customer1' and
t2.created_on >= to_timestamp('15-NOV-2013','dd-MON-yyyy')
order by t2.created_on desc limit 1) t2max
group by t1.txnid, t1.account_id, t1.result;