SQL 为每个父行从子级返回最大值

我有 2 个表 - 1 个带有父记录，1 个带有子记录。对于每个父记录，我试图用 MAX(SalesPriceEach) 返回单个子记录。

另外，当有超过 1 个子记录时，我只想返回一个值。

parent - SalesTransactions table:
+-------------------+---------+
|SalesTransaction_ID|   text  |
+-------------------+---------+
| 1                 |  Blah   |
| 2                 |  Blah2  |
| 3                 |  Blah3  |
+-------------------+---------+

child - SalesTransactionLines table
+--+-------------------+---------+--------------+
|id|SalesTransaction_ID|StockCode|SalesPriceEach|
+--+-------------------+---------+--------------+
| 1|   1               |  123    | 99           |
| 2|   1               |   35    | 50           |
| 3|   2               |   15    | 75           |
+--+-------------------+---------+--------------+


 desired results
+-------------------+---------+--------------+
|SalesTransaction_ID|StockCode|SalesPriceEach|
+-------------------+---------+--------------+
|   1               |  123    | 99           |
|   2               |   15    | 75           |
+-------------------+---------+--------------+

我发现了一个非常相似的问题 here ，并根据答案进行查询，但没有看到我期望的结果。

WITH max_feature AS (
   SELECT c.StockCode,
          c.SalesTransaction_ID,
          MAX(c.SalesPriceEach)  as feature
     FROM SalesTransactionLines c
 GROUP BY c.StockCode, c.SalesTransaction_ID)
   SELECT p.SalesTransaction_ID,
          mf.StockCode,
          mf.feature
     FROM SalesTransactions p
LEFT JOIN max_feature mf ON mf.SalesTransaction_ID = p.SalesTransaction_ID

此查询的结果为每个父项返回多行，甚至不是最先返回的最高值!

最佳答案

select stl.SalesTransaction_ID, stl.StockCode, ss.MaxSalesPriceEach
from SalesTransactionLines stl
inner join
(
    select stl2.SalesTransaction_ID, max(stl2.SalesPriceEach) MaxSalesPriceEach
    from SalesTransactionLines stl2
    group by stl2.SalesTransaction_ID
    having count(*) > 1
) ss on (ss.SalesTransaction_ID = stl.SalesTransaction_ID and
         ss.MaxSalesPriceEach = stl.SalesPriceEach)

或者，或者:

SELECT stl1.*
FROM SalesTransactionLines AS stl1
LEFT OUTER JOIN SalesTransactionLines AS stl2
ON (stl1.SalesTransaction_ID = stl2.SalesTransaction_ID
    AND stl1.SalesPriceEach < stl2.SalesPriceEach)
WHERE stl2.SalesPriceEach IS NULL;

关于SQL 为每个父行从子级返回最大值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/7597995/