在子查询(Oracle)中引用父查询列

2022-01-23 00:00:00 oracle11g subquery oracle

如何使用 Oracle 引用子查询之外的列?我特别需要在子查询的 WHERE 语句中使用它.

How can I reference a column outside of a subquery using Oracle? I specifically need to use it in the WHERE statement of the subquery.

基本上我有这个:

SELECT Item.ItemNo, Item.Group
FROM Item
  LEFT OUTER JOIN (SELECT Attribute.Group, COUNT(1) CT 
                   FROM Attribute 
                   WHERE Attribute.ItemNo=12345) A ON A.Group = Item.Group
WHERE Item.ItemNo=12345

我想在子查询中将 WHERE Attribute.ItemNo=12345 更改为 WHERE Attribute.ItemNo=Item.ItemNo，但我不知道是否这个有可能.我不断收到ORA-00904: 'Item'.'ItemNo': Invalid Identifier"

I'd like to change WHERE Attribute.ItemNo=12345 to WHERE Attribute.ItemNo=Item.ItemNo in the subquery, but I can't figure out if this is possible. I keep getting "ORA-00904: 'Item'.'ItemNo': Invalid Identifier"

好的，这就是我需要这种结构的原因:

Ok, this is why I need this kind of structure:

我希望能够获得错误"记录(项目缺少值)和正常"记录(项目有值)的计数.

I want to be able to get a count of the "Error" records (where the item is missing a value) and the "OK" records (where the item has a value).

我在小提琴中设置的方式返回正确的数据.我想我最终可能只是填写每个子查询中的值，因为这可能是最简单的方法.对不起，如果我的数据结构有点复杂.如果需要，我可以解释.

The way I have set it up in the fiddle returns the correct data. I think I might just end up filling in the value in each of the subqueries, since this would probably be the easiest way. Sorry if my data structures are a little convoluted. I can explain if need be.

我的桌子是:

create table itemcountry(
  itemno number,
  country nchar(3),
  imgroup varchar2(10),
  imtariff varchar2(20),
  exgroup varchar2(10),
  extariff varchar2(20) );

create table itemattribute(
  attributeid varchar2(10),
  tariffgroup varchar2(10),
  tariffno varchar2(10) );

create table icav(
  itemno number,
  attributeid varchar2(10),
  value varchar2(10) );

到目前为止我的查询是:

and my query so far is:

select itemno, country, imgroup, imtariff, im.error "imerror", im.ok "imok", exgroup, extariff, ex.error "exerror", ex.ok "exok"
from itemcountry
  left outer join (select sum(case when icav.itemno is null then 1 else 0 end) error, sum(case when icav.itemno is not null then 1 else 0 end) ok, tariffgroup, tariffno
                   from itemattribute ia 
                     left outer join icav on ia.attributeid=icav.attributeid 
                   where (icav.itemno=12345 or icav.itemno is null) 
                   group by tariffgroup, tariffno) im on im.tariffgroup=imgroup and imtariff=im.tariffno
  left outer join (select sum(case when icav.itemno is null then 1 else 0 end) error, sum(case when icav.itemno is not null then 1 else 0 end) ok, tariffgroup, tariffno
                   from itemattribute ia 
                     left outer join icav on ia.attributeid=icav.attributeid 
                   where (icav.itemno=12345 or icav.itemno is null) 
                   group by tariffgroup, tariffno) ex on ex.tariffgroup=exgroup and extariff=ex.tariffno
where itemno=12345;

它也在 SQL Fiddle 中设置.

推荐答案

您可以在子查询中执行此操作，但不能在联接中执行此操作.在你的情况下，我认为没有必要.你可以把它放在join条件中.

You can do it in a sub-query but not in a join. In your case I don't see any need to. You can put it in the join condition.

select i.itemno, i.group
  from item i
  left outer join ( select group, itemno
                      from attribute b
                     group by group itemno ) a
    on a.group = i.group 
   and i.itemno = a.itemno
 where i.itemno = 12345

优化器是为处理这种情况而设计的，所以好好利用它吧！

The optimizer is built to deal with this sort of situation so utilise it!

我已将 count(1) 更改为 group by 因为您需要 group by 所有未聚合的列.

I've changed the count(1) to a group by as you need to group by all columns that aren't aggregated.

我假设您的实际查询比这更复杂，因为您选择的列可能相当于

I'm assuming that your actual query is more complicated than this as with the columns you're selecting this is probably equivilent to

select itemno, group
  from item
 where itemno = 12345

您还可以使用 分析函数 取而代之.类似于 count(*) over (partition by group).

顺便说一句，使用关键字作为列名，在这种情况下 group 是一个坏主意 TM.它会引起很多混乱.从上面的代码可以看出，里面有很多groups.

As an aside using a keyword as a column name, in this case group is A Bad Idea TM. It can cause a lot of confusion. As you can see from the code above you have a lot of groups in there.

因此，根据您的 SQL-Fiddle，我已将其添加到我认为您正在寻找类似以下内容的问题中，这看起来并没有好多少.我怀疑，如果有时间，我可以让它变得更简单.另一方面，明确的小写查询永远不值得它引起的麻烦.不过我遵循了你的命名约定.

So, based on your SQL-Fiddle, which I've added to the question I think you're looking for something like the following, which doesn't look much better. I suspect, given time, I could make it simpler. On another side note explicitly lower casing queries is never worth the hassle it causes. I've followed your naming convention though.

with sub_query as (
 select count(*) - count(icav.itemno) as error
      , count(icav.itemno) as ok
      , min(itemno) over () as itemno
      , tariffgroup
      , tariffno
   from itemattribute ia 
   left outer join icav 
     on ia.attributeid = icav.attributeid 
  group by icav.itemno
         , tariffgroup
         , tariffno
  ) 
    select ic.itemno, ic.country, ic.imgroup, ic.imtariff
         , sum(im.error) as "imerror", sum(im.ok) as "imok"
         , ic.exgroup, ic.extariff
         , sum(ex.error) as "exerror", sum(ex.ok) as "exok"
      from itemcountry ic
      left outer join sub_query im 
        on ic.imgroup = im.tariffgroup
       and ic.imtariff = im.tariffno
       and ic.itemno = im.itemno
      left outer join sub_query ex 
        on ic.exgroup = ex.tariffgroup
       and ic.extariff = ex.tariffno
       and ic.itemno = ex.itemno
     where ic.itemno = 12345
     group by ic.itemno, ic.country
            , ic.imgroup, ic.imtariff
            , ic.exgroup, ic.extariff
           ;

相关文章