取得客戶ID和第20次交易日期的MySQL - SQL查詢
P粉604848588
P粉604848588 2024-02-17 13:35:57
0
2
471

我無法提出查詢來取得客戶 ID 清單及其第 20 次購買的日期。

我拿到了一個名為 transactions 的表,其中欄位名為 customer_id 和purchase_date。表中的每一行都等於一筆交易。

customer_id 購買日期
1 2020-11-19
2 2022-01-01
3 2021-12-05
3 2021-12-09
3 2021-12-16

我嘗試這樣做,並假設我必須計算 customer_id 被提及的次數,如果計數等於 20,則傳回 id 編號。

SELECT customer_id, MAX(purchase_date)
FROM transactions
(
     SELECT customer_id,
     FROM transactions
     GROUP BY customer_id
     HAVING COUNT (customer_id) =20
)

如何讓它傳回 customer_id 清單以及第 20 次交易的日期?

P粉604848588
P粉604848588

全部回覆(2)
P粉724737511

我的解決方案:

select *
from transactions t
inner join (
   select 
      customer_id,
      purchase_date,
      row_number() over (partition by customer_id order by purchase_date) R
   from transactions) x on x.purchase_date=t.purchase_date
                       and x.customer_id=t.customer_id
where x.R=20;

請參閱:DBFIDDLE

對於MySQL5.7,請參考:DBFIDDLE

set @r:=1;
select *
from transactions t
inner join (
   select 
      customer_id,
      purchase_date,
      @r:=@r+1 R
   from transactions) x on x.purchase_date=t.purchase_date
                       and x.customer_id=t.customer_id
where x.R=20;
P粉043295337

您需要選擇屬於customer_id的交易行,並按第20行過濾結果

SELECT * FROM (
    SELECT customer_id, purchase_date, ROW_NUMBER() OVER(
        PARTITION BY customer_id
        ORDER BY purchase_date DESC
    ) AS nth
    FROM transactions
) as t WHERE nth = 20
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板