将常规选择语句转换为相关子查询？

I have my following SQL query:

我有以下SQL查询：

 SELECT name, transaction_id, MIN(transaction_date)
 FROM clients
 JOIN transactions ON clients.client_id = transactions.client_id
 GROUP BY name, transaction_id;

That I'd like to turn into a correlated subquery with the following structure:

我想变成一个具有以下结构的相关子查询：

 SELECT a, b, MIN(c)
 FROM t1
 JOIN t2 ON t1.d = t2.d
 WHERE c IN
      (SELECT * 
      FROM t2
      HAVING....)

Where a, b, c are column-names and t1, t2 are tables.

其中a，b，c是列名，t1，t2是表。

But I'm having difficulty in the process.

但是我在这个过程中遇到了困难。

For reference, the original problem is asking to return the earliest transaction_date of each customer along with its corresponding transaction_id.

作为参考，最初的问题是要求返回每个客户的最早的transaction_date及其对应的transaction_id。

So, if the transactions table had the following:

因此，如果事务表具有以下内容：

   transaction_id    client_id      transaction_date
         1               1              02-02-17
         2               1              02-01-17
         3               2              02-03-17
         4               2              02-04-17

The correlated subquery would return:

2 个解决方案

#1

Your query does not do what you think it does. A proper query would be:

您的查询不符合您的想法。一个正确的查询将是：

SELECT c.name, t.transaction_id, t.transaction_date
FROM clients c JOIN
     transactions t
     ON c.client_id = t.client_id
WHERE t.transaction_date = (SELECT MIN(t2.transaction_date)
                            FROM transactions t2
                            WHERE t2.client_id = t.client_id
                           );

A more typical query would be:

更典型的查询是：

SELECT name, transaction_id, transaction_date
FROM (SELECT c.name, t.transaction_id, t.transaction_date,
             ROW_NUMBER() OVER (PARTITION BY c.client_id ORDER BY t.transaction_date) as seqnum
      FROM clients c JOIN
           transactions t
           ON c.client_id = t.client_id 
     ) ct
WHERE seqnum = 1;

#2

In oracle 12c there are CROSS APPLY and OUTER APPLY clauses:
(look for cross_outer_apply_clause in this link):

在oracle 12c中有CROSS APPLY和OUTER APPLY子句:(在此链接中查找cross_outer_apply_clause）：

cross_outer_apply_clause

cross_outer_apply_clause

This clause allows you to perform a variation of an ANSI CROSS JOIN or an ANSI LEFT OUTER JOIN with left correlation support. You can specify a table_reference or collection_expression to the right of the APPLY keyword. The table_reference can be a table, inline view, or TABLE collection expression. The collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. The table_reference or collection_expression can reference columns of tables defined in the FROM clause to the left of the APPLY keyword. This is called left correlation.

此子句允许您使用左关联支持执行ANSI CROSS JOIN或ANSI LEFT OUTER JOIN的变体。您可以在APPLY关键字的右侧指定table_reference或collection_expression。 table_reference可以是表，内联视图或TABLE集合表达式。 collection_expression可以是子查询，列，函数或集合构造函数。无论其形式如何，它都必须返回一个集合值 - 即类型为嵌套表或varray的值。 table_reference或collection_expression可以引用APPLY关键字左侧FROM子句中定义的表的列。这称为左相关。

These two clauses have a (left) correlation support - this simply means that correlated subqueries can be used.

Your query might look like this:

这两个子句有一个（左）相关支持 - 这只是意味着可以使用相关的子查询。您的查询可能如下所示：

select c.*, x.*
from clients c
cross apply (
   select transaction_id, transaction_date
   from transactions t
   where t.client_id = c.client_id
   order by transaction_date desc
   fetch first row only
) x

or using outer apply:

或使用外部申请：

select c.*, x.*
from clients c
outer apply (
   select transaction_id, transaction_date
   from transactions t
   where t.client_id = c.client_id
   order by transaction_date desc
   fetch first row only
) x

The latter query is like LEFT JOIN - it gives all clients including clients that have no any transaction, while the former one is like INNER JOIN and lists only clients that have at least 1 transaction.

Both queries use correlated subqueries on the right side,
these subqueries use where t.client_id = c.client_id condition which references a table on the left side.

后一个查询就像LEFT JOIN一样 - 它为所有客户端提供包括没有任何事务的客户端，而前一个客户端就像INNER JOIN，只列出至少有1个事务的客户端。两个查询都使用右侧的相关子查询，这些子查询使用t.client_id = c.client_id条件，该条件引用左侧的表。

#1