我有一個包含以下列的事務表：TRANSACTION_ID,USER_ID,MERCHANT_NAME,TRANSACTION_DATE,AMOUNT
-1) query to find the first merchant a user transacts on
-2) query to find the last merchant a user transacts on
我嘗試了以下
此查詢應作為組作業，從第一個條目中獲取分類值
-)select USER_ID, Min(TRANSACTION_DATE),MERCHANT_NAME from transactions
Group by USER_ID
uj5u.com熱心網友回復：
使用ROW_NUMBER我們可以嘗試：
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY TRANSACTION_DATE) rn1,
ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY TRANSACTION_DATE DESC) rn2
FROM transactions
)
SELECT USER_ID,
MAX(CASE WHEN rn1 = 1 THEN MERCHANT_NAME END) AS FIRST_MERCHANT_NAME,
MAX(CASE WHEN rn2 = 1 THEN MERCHANT_NAME END) AS LAST_MERCHANT_NAME
FROM cte
GROUP BY USER_ID;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/433448.html
標籤：mysql sql 熊猫-groupby
上一篇：SpringBootREST-MYSQL連接不起作用
下一篇：如何在連接中使用聚合函式？