如何检索SQL中嵌套表的列
如何检索SQL中嵌套表的列
提问于 2022-09-02 11:11:35
这可能是一个基本问题，但是如何在使用NOT in函数时从内表中检索第2列？
例如，我有两个表Customer(Customer id，customername) Orders (订单id，Order date，customerid)，这里的customer id是格式化的外键。
我正在写一个查询
select *
from customer
where customerid not in (select case when '1' then substr(customerid,1,2)
when '2' then substr(customerid , 1,3)
else customerid
end from Orders)在此查询中，如何检索订单id和订单日期？
提前感谢
回答 2
Stack Overflow用户
发布于 2022-09-02 19:27:42
试一试：
select customer.*, orders.orderid, orders.orderdate
from customer
full outer join
(
select case when substr(customerid,1,2) = '1' then substr(customerid,1,2)
when substr(customerid,1,3) = '2' then substr(customerid,1,3)
else customerid
end as customid
, orderid, orderdate
from Orders
) orders on (customer.customerid = orders.customid)
where customer.customerid is null
;示例：
with customer as (
select 'James' as name, '100' as customerid from dual
union all
select 'Mike' as name, '101' as customerid from dual
union all
select 'Steve' as name, '102' as customerid from dual
union all
select 'Nora' as name, '103' as customerid from dual
),
orders as (
select 1001 as orderid, TO_DATE('2020-06-01T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') as orderdate, '100' as customerid from dual
union all
select 1002 as orderid, TO_DATE('2020-07-21T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') as orderdate, '102' as customerid from dual
union all
select 1003 as orderid, TO_DATE('2020-07-22T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') as orderdate, '2' as customerid from dual
union all
select 1004 as orderid, TO_DATE('2020-06-05T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') as orderdate, '103' as customerid from dual
union all
select 1005 as orderid, TO_DATE('2020-06-05T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS') as orderdate, '101' as customerid from dual
)
select customer.*, orders.orderid, orders.orderdate
from customer
full outer join
(
select case when substr(customerid,1,2) = '1' then substr(customerid,1,2)
when substr(customerid,1,3) = '2' then substr(customerid,1,3)
else customerid
end as customid
, orderid, orderdate
from Orders
) orders on (customer.customerid = orders.customid)
where customer.customerid is null
;对我来说不太清楚的是你的客户身份格式化的外键。你能举几个例子吗？
Stack Overflow用户
发布于 2022-09-02 11:17:46
试试这个：
select c.customerid, c.customername, o.id, o.date
from orders o, customer c
where not exists (select 1
from orders o2
where c.customerid = o2.customerid
and o.id = o2.id);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73581666
复制相关文章
相似问题
腾讯云开发者
