如何在SQL中加载最后一条记录 [英] How to load last record in SQL

问题描述

我有四个表

i have Four table

tblUser(UserID PK,name)
tblPackage (PackageID  pk, PackageName)
tblPayment(PaymentID Pk,UserID Fk, PackageID FK,Payingdate,PaidAmount,RemainingBalance)
tblsubscription(subID pk, userID, packageID,PackageExpireDate, PackStartDate)

这里假设在我的tblpaymet中我添加了值

Here suppose in my tblpaymet i am adding the value

(1,1,1,2016-11-2,2000,8000),
(2,1,1,2016-11-3,3000,7000),
(3,1,1,2016-11-4,2000,8000)

如果同一个用户拿另一个包

and if same user take another package

(4,1,2,2016-11-1,5000,7000),
(5,1,2,2016-11-2,5000,7000),
(6,1,2,2016-11-3,5000,7000),

在这两个语句中我想通过区分它们来回复每个用户的最后一条记录packageID意味着在我的例子中我想要回溯只有paymentid（3,6），但使用此查询我得到所有记录。所以，请帮助我获取每个用户的最后一条记录



我尝试了什么：

in this both statement i want to retrive the last record of each user by differentiate them packageID means in my example i want to retrive only paymentid(3,6) but using this query i am getting all record. so please help me to get the last record of each user

What I have tried:

select  users.UserID, users.Name, users.Contact, users.photos, pay.PaymentID, pay.Paying_date, pay.Payble, pay.Discount, pay.Total_Paid,p ay.Balance_Remain, pay.Payment_Mode, pay.Payment_Type, pay.Payment_Describtion, pack.PackageID, pack.Package_Name, pack.Package_Duration, pack.Package_Amount, sub.Package_Expire_Date, sub.Package_StartDate from tblUser as users 
	inner join tblPayment as pay 
	on users.UserID=pay.UserId
	left outer join tblPackages as pack
	on pack.PackageID = pay.PackageId
	inner join tblSubscription as sub 
	on users.UserID = sub.UID

推荐答案

这样的事情应该有效：

Something like this should work:

WITH ctePayment As
(
    SELECT
        PaymentID,
        UserID,
        PackageID,
        Paying_date, 
        Payble,
        Discount, 
        Total_Paid,
        Balance_Remain, 
        Payment_Mode, 
        Payment_Type, 
        Payment_Description, 
        ROW_NUMBER() OVER (PARTITION BY UserID, PackageID ORDER BY Paying_date DESC) As RN
    FROM
        tblPayment
)
SELECT 
    users.UserID, 
    users.Name, 
    users.Contact, 
    users.photos, 
    pay.PaymentID, 
    pay.Paying_date, 
    pay.Payble,
    pay.Discount, 
    pay.Total_Paid,
    pay.Balance_Remain, 
    pay.Payment_Mode, 
    pay.Payment_Type, 
    pay.Payment_Description, 
    pack.PackageID, 
    pack.Package_Name,
    pack.Package_Duration, 
    pack.Package_Amount, 
    sub.Package_Expire_Date, 
    sub.Package_StartDate 
FROM
    tblUser As users 
    INNER JOIN ctePayment As pay 
    ON pay.UserId = users.UserID 
    And pay.RN = 1
    INNER JOIN tblSubscription as sub 
    ON sub.UID = users.UserID
    And sub.PackageID = pay.PackageUD
    INNER JOIN tblPackages As pack
    ON pack.PackageID = pay.PackageID
;

这篇关于如何在SQL中加载最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！