在子查询 (Oracle) 中引用父查询列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10092320/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reference parent query column in subquery (Oracle)
提问by tedski
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.
如何使用 Oracle 引用子查询之外的列？我特别需要在子查询的 WHERE 语句中使用它。
Basically I have this:
基本上我有这个：
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
I'd like to change WHERE Attribute.ItemNo=12345to WHERE Attribute.ItemNo=Item.ItemNoin the subquery, but I can't figure out if this is possible. I keep getting "ORA-00904: 'Item'.'ItemNo': Invalid Identifier"
我想在子查询中更改WHERE Attribute.ItemNo=12345为WHERE Attribute.ItemNo=Item.ItemNo，但我不知道这是否可行。我不断收到“ORA-00904: 'Item'.'ItemNo': Invalid Identifier”
EDIT:
编辑：
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.
我在小提琴中设置它的方式返回正确的数据。我想我可能最终会填写每个子查询中的值，因为这可能是最简单的方法。对不起，如果我的数据结构有点复杂。如果需要，我可以解释。
My tables are:
我的表是：
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;
It's also set up in a SQL Fiddle.
它也在SQL Fiddle 中设置。
采纳答案by Ben
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!
优化器是为处理这种情况而构建的，因此请使用它！
I've changed the count(1)to a group byas you need to group byall columns that aren't aggregated.
对于所有未聚合的列，我已根据您的需要将 the 更改count(1)为 a 。group bygroup by
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
You could also write your sub-query with an analytic functioninstead. Something like count(*) over ( partition by group).
你也可以用一个analytic function代替来编写你的子查询。类似的东西count(*) over ( partition by group)。
As an aside using a keyword as a column name, in this case groupis A Bad Idea TM. It can cause a lot of confusion. As you can see from the code above you have a lot of groupsin there.
顺便说一句，使用关键字作为列名，在这种情况下group是 A Bad Idea TM。它会引起很多混乱。从上面的代码可以看出groups，里面有很多。
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.
因此，根据您的 SQL-Fiddle，我已将其添加到问题中，我认为您正在寻找类似以下内容的内容，但看起来并没有好多少。我怀疑，如果有时间，我可以让它更简单。另一方面，明确的小写查询永远不值得它造成的麻烦。不过，我遵循了您的命名约定。
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
;
回答by Richard Chung
You can put WHERE attribute.itemno=item.itemno inside the subquery. You are going to filter the data anyway, filtering the data inside the subquery is usually faster too.
您可以将 WHERE attribute.itemno=item.itemno 放在子查询中。无论如何，您都将过滤数据，过滤子查询中的数据通常也更快。
