如何在SQL Server中获得每个组/分区的最大行数?

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.

我使用的是SQL Server 2005。我有一个支付表，包括支付id、用户id和时间戳。我想找到每个用户最近的支付。这很容易搜索和找到答案。我还想知道的是最近的付款是否是用户的第一次付款。

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

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?

我不是在做心理跳跃然后让我选择每个用户的最高支付数。如果我使用MAX(paymentNumber)将上面的内容作为子选择，然后使用user_id进行分组，那么我将丢失我需要的payment_id。但是如果我也将payment_id添加到group by子句中，那么每次支付我都会返回一行。我确信我忽略了显而易见的事实。任何帮助吗?

5 个解决方案

#1

Try this:

试试这个:

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

#2

Do the same thing again.

再做同样的事情。

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

Now the most recent payment has reversePaymentNumber 1, and the number of payments will be paymentNumber.

现在，最近的付款方式是反向支付1，支付的金额将是支付号码。

#3

; with cte as (
SELECT
    p.payment_id,
    p.user_id,
    ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.payment_date desc) AS paymentNumber
FROM
    payment p
) select * from cte where paymentNumber = 1

#4

a less cool way i suppose

一种不太酷的方式

; with maxp as
(
    select
        p.user_id,
        max(p.payment_date) as MaxPaymentDate
    from payment p
    group by p.userid
),
nump as
(
    select
        p.payment_id,     
        p.user_id,     
        p.payment_date,
        ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.payment_date) AS paymentNumber 
    FROM payment p
),
a as
(
select
    nump.payment_id,
    nump.user_id,
    nump.paymentNumber
    case when maxp.MaxPaymentDate is null then 'Old' else 'New' end as NewState
from nump
    left outer join maxp
        on nump.user_id=maxp.user_id
            and nump.payment_date=maxp.MaxPaymentDate
)

select
*
from a
where NewState='New'

#5

How about this?

这个怎么样?

SELECT
    p.user_id,
    MAX(p.payment_date) as lastPayment,
    CASE COUNT(p.payment_id) WHEN 1 THEN 1 ELSE 0 END as isFirstPayment
FROM
    payment p
GROUP BY
    p.user_id

#1