sql - SQL，根据分组选择的最小值从第二个表中获取值

我在下面有一个查询，我列出了所有没有为空的交易，目的是将所有打开的transactionID（代表一个客户订单）及其发票编号转到一个列表中。

Table 1
transactionID
bookdate
cost
year

Table 2
transactionID
invoice
year

SELECT 1.transactionID, Sum(cost), 1.Max(bookdate), 2.invoice
FROM 1
LEFT JOIN 2
ON 1.transactionID = 2. transactionID AND 1.year = 2.year
GROUP BY 1.transactionID, 2.invoice
HAVING (Sum(cost)<>0)

我的问题是transactionID每年都会重复使用，这就是为什么我需要检查实际transactionID和表2中的发票之间年份是否对应的原因。

每个transactionID都有几笔具有不同预订日期的交易。这意味着可能在2011年发生了一笔交易，在2012年发生了一笔交易。我希望查询查找与每个打开的transactionID最早的预订日期相对应的年份。

例如：

表格1

1 | 20120101 | -20  | 2012
2 | 20120501 | -100 | 2012
2 | 20110501 | 100  | 2012
1 | 20110801 | 50   | 2011

表2

1 | invoice X   | 2012
2 | invoice Y   | 2012
1 | invoice old | 2011

结果应该是

1 | 30 USD | Invoice old

最佳答案

如果使用的是SQL Server 2005或更高版本，则可以使用如下的window函数：

WITH summedAndRanked AS (
  SELECT
    [1].transactionID,
    [2].invoice,
    totalCost    = SUM(cost)     OVER (PARTITION BY [1].transactionID),
    lastBookDate = MAX(bookdate) OVER (PARTITION BY [1].transactionID),
    rnk          = DENSE_RANK()  OVER (PARTITION BY [1].transactionID ORDER BY [1].year),
  FROM [1]
    LEFT JOIN [2]
      ON [1].transactionID = [2].transactionID
     AND [1].year = [2].year
)
SELECT DISTINCT
  TransactionID,
  totalCost,
  lastBookDate,
  invoice
FROM countedAndRanked
WHERE totalCost <> 0
  AND rnk = 1
;

关于sql - SQL，根据分组选择的最小值从第二个表中获取值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12279624/

transactionId

sql - SQL，根据分组选择的最小值从第二个表中获取值