SSRS所需的SQL逻辑-来自多个结果的最大日期条目

Question

我有一个情况，有两个表SS和SSUSED。表SS列出了所有使用次数有限的票证。表2,SSUSED包含表SS中所有门票的条目。我需要从两个表中收集某些信息，但是，我只需要从表SSUSED中具有最近日期的行中获取数据。

我的代码从表SSUSED中的所有用法行返回指定的值。谁能帮我弄一下逻辑，只从SSUSED的最新条目中检索所需的字段。

SELECT        SS.memid, SS.initialdate, SS.dateexpire, SS.initialquantity, SS.usedquantity, PRODUCTS.description, EMPLOYEES_1.lname AS trainlname, EMPLOYEES_1.fname AS trainfname, SS.ssid
FROM            SS INNER JOIN
PRODUCTS ON SS.productid = PRODUCTS.productid INNER JOIN
SSUSED ON SS.ssid = SSUSED.ssid INNER JOIN
EMPLOYEES AS EMPLOYEES_1 ON SSUSED.employeeid = EMPLOYEES_1.employeeid
WHERE (SS.initialdate BETWEEN @rvStartDate AND @rvEndDate) 

Answer 1

您可以使用派生列对SSUSED记录进行排序。然后将该表与您的数据连接起来，并在连接字段中使用最新的指示符。

;WITH MostRecentSSUSED AS
(
SELECT ssid, employeid,
ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY ssid DESC) AS MostRecentID
FROM
SSUSED
)
SELECT        SS.memid, SS.initialdate, SS.dateexpire, SS.initialquantity, SS.usedquantity, PRODUCTS.description, EMPLOYEES_1.lname AS trainlname, EMPLOYEES_1.fname AS trainfname, SS.ssid
FROM            SS INNER JOIN
PRODUCTS ON SS.productid = PRODUCTS.productid INNER JOIN
MostRecentSSUSED SSUSED ON SS.ssid = SSUSED.ssid AND SSUSED.MostRecentID = 1 INNER JOIN
EMPLOYEES AS EMPLOYEES_1 ON SSUSED.employeeid = EMPLOYEES_1.employeeid
WHERE (SS.initialdate BETWEEN @rvStartDate AND @rvEndDate) 

你也可以用另一种方法试试。根据SSUSED的大小，下面的查询可能更有效，因为部分数据将首先被过滤掉，然后再进行排名。

SELECT
*
FROM
(
SELECT        SS.memid, SS.initialdate, SS.dateexpire, SS.initialquantity, SS.usedquantity, PRODUCTS.description, EMPLOYEES_1.lname AS trainlname, EMPLOYEES_1.fname AS trainfname, SS.ssid,
ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY ssid DESC) AS MostRecentID
FROM            SS INNER JOIN
PRODUCTS ON SS.productid = PRODUCTS.productid INNER JOIN
MostRecentSSUSED SSUSED ON SS.ssid = SSUSED.ssid  INNER JOIN
EMPLOYEES AS EMPLOYEES_1 ON SSUSED.employeeid = EMPLOYEES_1.employeeid
WHERE (SS.initialdate BETWEEN @rvStartDate AND @rvEndDate) 
)AS X
WHERE 
X.MostRecentID = 1