如何获取关系表的第一行(取决于更改的最后日期)

问题描述

大家好，
我想填写一个网格(无层次结构)，在该网格中将客户数据和每位客户的最后一个销售项连续组合(仅根据sells_date获得最后的销售).
我的想法是

Hi all,
I want to fill a grid (no hirarchical) where to combine customer data and the last selling item of each customer in a row (getting only the last sellings depending on sells_date).
My idea was

SELECT C.id,C.customerdata,S.id,S.sellsdata FROM dbo.customer AS C LEFT OUTER JOIN (SELECT TOP 1 id,sellsdata FROM dbo.sells ORDER BY sells_date DESC) AS S ON C.id=S.id<br />

用这个字符串我只得到一个销售项目，而每个项目却没有一个项目.
正确的SQL字符串看起来如何?

tnx提前
Frank

Whith this string I get only one selling item and not one item for each.
How does the correct SQL-String look likes ?

tnx in advance
Frank

推荐答案

;WITH LatestSells AS (<br />	SELECT s1.* <br />	FROM dbo.Sells AS s1<br />	INNER JOIN (<br />		SELECT DISTINCT Customer_ID<br />			, (	SELECT TOP 1 Sell_ID <br />				FROM dbo.Sells <br />				WHERE Customer_ID = sx.Customer_ID <br />				ORDER BY Sell_Date DESC <br />			  ) AS Sell_ID<br />		FROM dbo.Sells AS sx<br />	) AS s2 ON s1.Customer_ID = s2.Customer_ID AND s1.Sell_ID = s2.Sell_ID<br />)<br />SELECT c.*, s.*<br />FROM  dbo.Customers			AS c<br />LEFT OUTER JOIN LatestSells AS s<br />ON c.Customer_ID = s.Customer_ID<br />

这篇关于如何获取关系表的第一行(取决于更改的最后日期)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！