问如何检索SQL中嵌套表的列
EN

Stack Overflow用户

提问于 2022-09-02 03:11:35

回答 2查看 47关注 0票数 0

这可能是一个基本问题，但是如何在使用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和订单日期？

提前感谢

oracle

sql

腾讯云推广大使邀新奖励

推荐好友上云最高获8万/月现金

回答 2

Stack Overflow用户

发布于 2022-09-02 11: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
;

对我来说不太清楚的是你的客户身份格式化的外键。你能举几个例子吗？

票数 0

Stack Overflow用户

发布于 2022-09-02 03: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);