如何在SQL中选择每个用户的最后一条记录 [英] How to select last record of each user in SQL

问题描述

你好我的数据库中有三个表，我想用userid和packID选择每个用户的最后一条记录

假设我的三个表如下表1（UserID，name，contact）

table2（PackID，包名）

table3（PayID，UserID，PackID）

这里我想要检索每个用户的最后一个payID用户两次选择相同的包然后我想要记录两次，例如如果第一行中的table3值是（1,1,1）而第二行中的值是（2,1,1）那么我想要这两个记录。但在我的查询中，我有他们用userID分组为什么它只显示最后一条记录我如何通过用userID和PackID将它们分组来获得最后一条记录



请帮帮我



我尝试了什么：



i使用了查询按照

从table1选择t1.UserID，t2.payID作为t1

内连接（选择最大（PayID）为payID，max（UserID）为来自table3组的UserID by UserID）as t2

on t1.UserID = t2.userID

hello i have three table in my database and i want to select the last record of each user by userid and packID
suppose my three table are as follow table1(UserID, name,contact)
table2(PackID, Packname)
table3(PayID,UserID,PackID)
here i want to retrive the last payID of each user if user has selected the same package two time then i want the record two time for example if table3 value in first row is (1,1,1) and in second row value are (2,1,1) then i want both this two record. but in my query i have group by them with userID that why it will just show the last record how can i get the last record by group them with userID and PackID

please help me

What I have tried:

i have used query is follow
select t1.UserID,t2.payID from table1 as t1
inner join (select max(PayID) as payID,max(UserID) as UserID from table3 group by UserID) as t2
on t1.UserID=t2.userID

推荐答案

你好

如果你是使用sql查询，你可以

1）OredrByAsc（* fieldName），显示最后一条记录，冷杉

2）选择top（1）选择第一条记录，最后一个



如果您使用的是Entity Framework：

您可以使用.LastOrDefault（）;检索最后一个



希望它有效

hello
if you are using sql query, you can
1) OredrByAsc(* fieldName), to show the last record, firs
2) Select top(1) to select the firs record, infact the last one

if you are using Entity Framework:
you can use .LastOrDefault(); to retrieve last one

hope it works

试试这个：

Try this:

;WITH cte1
AS (
	SELECT PAYID
		,USERID
		,PACKID
		,RANK() OVER (
			PARTITION BY USERID ORDER BY PAYID DESC
			) AS Rnk
	FROM table3
	)
	,cte2
AS (
	SELECT PAYID
		,USERID
		,PACKID
	FROM cte1
	WHERE Rnk = 1
	)
	
	SELECT t3.PAYID
		,t3.USERID
		,t3.PACKID
	FROM table3 t3
	INNER JOIN cte2 ON t3.PAYID <= cte2.PAYID
		AND t3.USERID = cte2.USERID
		AND t3.PACKID = cte2.PACKID

我做了一些假设：

1. PAYID会增加随着越来越多的记录被添加。换句话说，PAYID越大，它越近。你对MAX（PAYID）的使用似乎表明我的假设是正确的。

2.如果那些重复与USERID相对应，你需要重复一次USERID，PACKID组合，PACKID组合对应于最新的PAYID那个用户。因此，重复可能超过2次。您将在我的查询中获得所有重复。

I made some assumptions:
1. PAYID will increase as more and more records get added. In other words, the greater the PAYID, the more recent it is. Your usage of MAX(PAYID) seems to suggest my assumption is correct.
2. You need all repetitions of a USERID, PACKID combo if those repetitions match the USERID, PACKID combo corresponding to the latest PAYID for that USER. So, there might be more than 2 repetitions. You will get all repetitions in my query.

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