获取客户ID和第20次交易日期的MySQL - SQL查询
P粉604848588
P粉604848588 2024-02-17 13:35:57
0
2
485

我无法提出查询来获取客户 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
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板