是否可能从子查询中获取多个值?

Is there any way to have a subquery return multiple columns in oracle db? (I know this specific sql will result in an error, but it sums up what I want pretty well)

有没有办法让子查询返回oracle db中的多个列?(我知道这个特定的sql会导致错误，但它很好地总结了我想要的东西)

select
    a.x,
    ( select b.y, b.z from b where b.v = a.v),
from a

I want a result like this:

我想要这样的结果:

a.x | b.y | b.z
---------------
1   | 2   | 3

I know it is possible to solve this problem through joins, but that is not what I am asking for.

我知道通过连接解决这个问题是可能的，但这不是我所要求的。

My Question is simply if there is any way, to get two or more values out of a subquery? Maybe some workaround using dual? So that there is NO actual join, but a new subquery for each row?

我的问题是，如果有任何方法可以从子查询中获得两个或多个值?也许用对偶来解决?所以没有实际的连接，而是为每一行创建一个新的子查询?

EDIT: This is a principle question. You can solve all these problems using join, I know. You do not need subqueries like this at all (not even for one column). But they are there. So can I use them in that way or is it simply impossible?

编辑:这是一个原则问题。我知道你可以用join来解决所有这些问题。您根本不需要像这样的子查询(甚至对于一列也不需要)。但他们有。所以我可以用那种方式使用它们吗?还是根本不可能?

7 个解决方案

#1

A Subquery in the Select clause, as in your case, is also known as a Scalar Subquery, which means that it's a form of expression. Meaning that it can only return one value.

Select子查询(在您的例子中)也称为标量子查询，这意味着它是表达式的一种形式。意味着它只能返回一个值。

I'm afraid you can't return multiple columns from a single Scalar Subquery, no.

恐怕您不能从单个标量子查询返回多个列，不能。

Here's more about Oracle Scalar Subqueries:

下面是关于Oracle标量子查询的更多信息:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions010.htm#i1033549

http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions010.htm i1033549

#2

It's incorrect, but you can try:

这是不正确的，但是你可以试试:

select
    a.x,
    ( select b.y from b where b.v = a.v) as by,
    ( select b.z from b where b.v = a.v) as bz
from a

you can also use subquery in join

您还可以在join中使用子查询

 select
        a.x,
        b.y,
        b.z
    from a
    left join (select y,z from b where ... ) b on b.v = a.v

或

   select
        a.x,
        b.y,
        b.z
    from a
    left join b on b.v = a.v

#3

Here are two methods to get more than 1 column in a scalar subquery (or inline subquery) and querying the lookup table only once. This is a bit convoluted but can be the very efficient in some special cases.

这里有两个方法可以在标量子查询(或内联子查询)中获取多于一列，并且只查询一次查找表。这有点复杂，但在某些特殊情况下是非常有效的。

You can use concatenation to get several columns at once:

您可以使用连接来同时获取多个列:

SELECT x, 
       regexp_substr(yz, '[^^]+', 1, 1) y,
       regexp_substr(yz, '[^^]+', 1, 2) z
  FROM (SELECT a.x,
               (SELECT b.y || '^' || b.z yz
                  FROM b
                 WHERE b.v = a.v)
                  yz
          FROM a)

You would need to make sure that no column in the list contain the separator character.

您需要确保列表中没有列包含分隔符字符。

You could also use SQL objects:

您还可以使用SQL对象:

CREATE OR REPLACE TYPE b_obj AS OBJECT (y number, z number);

SELECT x, 
       v.yz.y y,
       v.yz.z z
  FROM (SELECT a.x,
               (SELECT b_obj(y, z) yz
                  FROM b
                 WHERE b.v = a.v)
                  yz
          FROM a) v

#4

Can't you use JOIN like this one?

你不能用这种连接吗?

SELECT
a.x , b.y, b.z 
FROM a 
LEFT OUTER JOIN b ON b.v = a.v

(I don't know Oracle Syntax. So I wrote SQL syntax)

我不知道Oracle的语法。我写了SQL语法

#5

In Oracle query

在Oracle中查询

select a.x
            ,(select b.y || ',' || b.z
                from   b
                where  b.v = a.v
                and    rownum = 1) as multple_columns
from   a

can be transformed to:

可以转化为:

select a.x, b1.y, b1.z
from   a, b b1
where  b1.rowid = (
       select b.rowid
       from   b
       where  b.v = a.v
       and    rownum = 1
)

Is useful when we want to prevent duplication for table A. Similarly, we can increase the number of tables:

当我们想要防止表a重复时是有用的。同样，我们可以增加表的数量:

.... where (b1.rowid,c1.rowid) = (select b.rowid,c.rowid ....

#6

you can use cross apply:

你可以使用交叉申请:

select
    a.x,
    bb.y,
    bb.z
from
    a
    cross apply
    (   select b.y, b.z
        from b
        where b.v = a.v
    ) bb

If there will be no row from b to mach row from a then cross apply wont return row. If you need such a rows then use outer apply

如果b和a之间没有行，那么交叉应用不会返回行。如果需要这样的行，请使用outer apply

If you need to find only one specific row for each of row from a, try:

如果您需要为a中的每一行找到一个特定的行，请尝试:

    cross apply
    (   select top 1 b.y, b.z
        from b
        where b.v = a.v
        order by b.order
    ) bb

#7

View this web: http://www.w3resource.com/sql/subqueries/multiplee-row-column-subqueries.php

把这个网站:http://www.w3resource.com/sql/subqueries/multiplee-row-column-subqueries.php

Use example

使用的例子

select ord_num, agent_code, ord_date, ord_amount
from orders
where(agent_code, ord_amount) IN
(SELECT agent_code, MIN(ord_amount)
FROM orders
GROUP BY agent_code);

从订单(agent_code, ord_amount)中选择ord_num、agent_code、ord_date、ord_amount(通过agent_code从orders组中选择agent_code、MIN(ord_amount));

#1