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

9o685dep 于 2022-12-10 发布在 SQL Server

关注(0)|答案(6)|浏览(199)

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

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?

sql-server

来源：https://stackoverflow.com/questions/4903527/how-to-get-the-max-row-number-per-group-partition-in-sql-server

6条答案

按热度按时间

u0njafvf1#

试试这个：

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

赞(0）回复(0）举报 2022-12-10

ruarlubt2#

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.

赞(0）回复(0）举报 2022-12-10

inb24sb23#

OP提供的查询完成了大部分工作。我们所需要做的就是将提供给ROW_NUMBER()的ORDER BY子句改为降序，在降序点，最新记录的值将为1。我选择使用CTE是出于个人喜好--子查询也可以。

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

赞(0）回复(0）举报 2022-12-10

6qfn3psc4#

一个不那么酷的方式我想

; 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'

赞(0）回复(0）举报 2022-12-10

nr7wwzry5#

SELECT * FROM (
        SELECT ROW_NUMBER() OVER(PARTITION BY OS.ContactId ORDER BY OS.Date ASC) AS FirstRow#,
        ROW_NUMBER() OVER(PARTITION BY OS.ContactId ORDER BY OS.Date DESC) AS LastRow#,
        OS.Contactid,CONVERT(VARCHAR,OS.Date,106) 'Purchase Month',
        OS.ProductId 'MyCII Subscription/Directory', OS.Charges 'Amount(INR)',OS.Date 'RAWDate'
        FROM tblOnlineServices OS
        WHERE Date IS NOT NULL AND Contactid IN('C000013112','C000010859') 
    ) FirstPurchase 
    WHERE FirstRow# = 1 OR LastRow# = 1
    ORDER BY Contactid, RAWDate

赞(0）回复(0）举报 2022-12-10

dluptydi6#

这样如何？

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

赞(0）回复(0）举报 2022-12-10

我来回答

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

6条答案

相关问题

热门标签

最新问答