SQL-为其他表中的每个键获取列的最小值

Question

我有两个这样的表：

订单

日期	TransactID	COL3
2021-06	1234		4
2021-09	1238		8

如果我做对了

SELECT SDP.TXN_ID, sdp.dt, datediff(sdp.dt, min(a1.DT)) diff
FROM SDP
JOIN AGG a1 on a1.UserID =
(SELECT a2.UserID 
FROM AGG a2 
WHERE SDP.TXN_ID = a2.TXN_ID
ORDER BY a2.UserID
limit 1)
GROUP BY SDP.TXN_ID, sdp.dt

您可以省略

ORDER BY a2.UserID
limit 1

前提是每个事务始终属于单个用户。

小提琴

基于您的SQL Fiddle(http://sqlfiddle.com/#！9/101497/1(这应该会让你开始

SELECT TXN_ID, DT, USERID
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY sdp.TXN_ID ORDER BY  sdp.DT ASC) AS [index],
sdp.TXN_ID,
sdp.DT,
agg.USERID 
FROM sdp
LEFT JOIN agg ON sdp.TXN_ID = agg.TXN_ID) A
WHERE [index] = 1

有关更多信息，您应该查看

https://www.sqlshack.com/sql-partition-by-clause-overview/

https://www.sqltutorial.org/sql-window-functions/sql-partition-by/

https://learnsql.com/blog/partition-by-with-over-sql/