如何在 SQL Server 中获取每个组/分区的最大行数? [英] How to get the max row number per group/partition in SQL Server?

问题描述

我使用的是 SQL Server 2005.我有一个包含付款 ID、用户 ID 和时间戳的付款表.我想为每个用户找到最近的付款.这很容易搜索并找到答案.不过，我还想知道最近的付款是否是用户的第一笔付款.

I'm using SQL Server 2005. I have a payments table with payment id's, user id's, and timestamps. I want to find the most recent payment for each user. This is easy to search and find an answer for. What I also want to know though is if the most recent payment is the user's first payment or not.

我有以下内容可以对每个用户的付款进行编号:

I have the following which will number each user's payments:

SELECT
    p.payment_id,
    p.user_id,
    ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.payment_date) AS paymentNumber
FROM
    payment p

我并没有做出精神上的飞跃，然后让我选择每个用户的最高付款数.如果我使用 MAX(paymentNumber) 将上述内容用作子选择，然后按 user_id 分组，则会丢失我需要的 payment_id.但是，如果我还将 payment_id 添加到 group by 子句中，则每次付款都会返回一行.我确定我忽略了显而易见的事情.有什么帮助吗?

I'm not making the mental leap which then lets me then pick the highest paymentNumber per user. If I use the above as a subselect by using MAX(paymentNumber) and then grouping by user_id, I lose the payment_id which I need. But if I also add the payment_id into the group by clause, I'm back to one row per payment. I'm sure I'm overlooking the obvious. Any help?

推荐答案

试试这个:

SELECT a.*, CASE WHEN totalPayments>1 THEN 'NO' ELSE 'YES' END IsFirstPayment
  FROM(
                SELECT  p.payment_id,     
                                p.user_id,     
                                ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.payment_date DESC) AS paymentNumber,
                                SUM(1) OVER (PARTITION BY p.user_id) AS totalPayments
                    FROM payment p 
            ) a
WHERE   paymentNumber = 1       

这篇关于如何在 SQL Server 中获取每个组/分区的最大行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！