在同一查询中选择多个记录中的最近记录
问题描述:
我的问题与sql select from multiple records only the most recent非常相关,虽然它根本不起作用。在同一查询中选择多个记录中的最近记录
我有一个view
,获取成员名称和他们当前的排名数字。 我也有一张表,它发现所有等级名称都大于上述等级号码。
按原样,新视图显示当前成员以及排名标题表中的排名标题>=
。
这意味着输出显示每个标题的排名后,而我只想显示第一个(最早)。
我已经尝试了变形例从上面的链接,像这样:
SELECT dbo.vwGetMemberRank.siteMemberId AS ID
, dbo.vwGetMemberRank.memberNickname AS Nick
, dbo.vwGetMemberRank.siteMemberRankLevel AS currentPoints
, dbo.tblSiteMemberRankTitle.siteMemberRankLevel AS neededPoints
, dbo.tblSiteMemberRankTitle.siteMemberRankTitle AS rankTitle
FROM dbo.vwGetMemberRank
INNER JOIN dbo.tblSiteMemberRankTitle
ON dbo.vwGetMemberRank.siteMemberRankLevel >= dbo.tblSiteMemberRankTitle.siteMemberRankLevel
WHERE dbo.vwGetMemberRank.siteMemberId IN
(
SELECT MIN(siteMemberId) AS ID
FROM dbo.vwGetMemberRank AS vwGetMemberRank_1
GROUP BY siteMemberRankLevel
)
这仍然显示每隔标题。
编辑
vwGetMemberRank
SELECT dbo.tblSiteMemberRank.siteMemberId
, dbo.tblSiteMemberRank.siteMemberRankLevel
, dbo.tblSiteMemberDetail.memberNickname
FROM dbo.tblSiteMemberDetail
INNER JOIN dbo.tblSiteMemberRank ON dbo.tblSiteMemberDetail.siteMemberId = dbo.tblSiteMemberRank.siteMemberId
tblSiteMemberRankTitle
CREATE TABLE [dbo].[tblSiteMemberRankTitle](
[id] [int] IDENTITY(1,1) NOT NULL,
[siteMemberRankTitle] [varchar](30) NOT NULL,
[siteMemberRankDescription] [varchar](80) NULL,
[siteMemberRankLevel] [int] NULL,
[thumbnailLocation] [nvarchar](100) NULL,
CONSTRAINT [PK__tblSiteM__3213E83F1B0907CE] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
答
可以使用ROW_NUMBER功能来订购你喜欢的任何顺序行列(我用dbo.tblSiteMemberRankTitle.siteMemberRankLevel DESC
),然后将结果限制在ROWNUMBER为1,因此只获得最高siteMemberRankLevel
行每个SiteMemberID(PARTITION BY)
WITH CTE AS
( SELECT dbo.vwGetMemberRank.siteMemberId AS ID
, dbo.vwGetMemberRank.memberNickname AS Nick
, dbo.vwGetMemberRank.siteMemberRankLevel AS currentPoints
, dbo.tblSiteMemberRankTitle.siteMemberRankLevel AS neededPoints
, dbo.tblSiteMemberRankTitle.siteMemberRankTitle AS rankTitle
, RowNumber = ROW_NUMBER() OVER(PARTITION BY dbo.vwGetMemberRank.siteMemberId ORDER BY dbo.tblSiteMemberRankTitle.siteMemberRankLevel DESC)
FROM dbo.vwGetMemberRank
INNER JOIN dbo.tblSiteMemberRankTitle
ON tblSiteMemberRankTitle.siteMemberRankLevel >= dbo.vwGetMemberRank.siteMemberRankLevel
)
SELECT ID, Nick, CurrentPoints, NeededPoints, RankTitle
FROM CTE
WHERE RowNumber = 1;
你检查出LAG()和铅( )? http://msdn.microsoft.com/en-us/library/hh213125.aspx – 2013-04-30 12:47:00
从来没有听说过。现在将阅读它。 - 你可以在这个过渡期间提供一个例子吗? – TheGeekZn 2013-04-30 12:47:26
基本上你创建一个窗口/偏移量来说“我想要这个函数的结果,对于离我很远的一行,当我命令所有这样的行时”。这是非常有效的,因为您不必自行加入任何东西;你只需重用你在FROM子句中的集合。 – 2013-04-30 12:49:20