只显示最近的日期的记录

Question

我有一个查询，它返回关于股票的信息，查询的作品，但你可以在下面的图片中看到有一些重复。我希望它在图像中显示，但不是重复我只希望它显示具有最新日期的行。

SELECT  TOP (100) PERCENT STOCK_CODE, TRANS_DATE, STOCK_QTY, DESCRIPTION, DETAIL 
FROM   Tencia.dbo.STK_TRANS 
WHERE  (TRANS_TYPE = N'STADJ') AND (STOCK_CODE LIKE 'LL%') AND (DESCRIPTION LIKE 'stock%') OR 
         (DESCRIPTION LIKE '%stk count%') 
GROUP BY STOCK_CODE, TRANS_DATE, STOCK_QTY, DESCRIPTION, DETAIL 
ORDER BY STOCK_CODE, TRANS_DATE DESC 

Answer 1

你可以通过做ROW_NUMBER()

;WITH CTE AS(
    SELECT 
     *, 
     rn = ROW_NUMBER() OVER(PARTITION BY STOCK_CODE ORDER BY TRANS_DATE DESC) 
    FROM Tencia.dbo.STK_TRANS 
    WHERE 
     (TRANS_TYPE = N'STADJ') 
     AND (STOCK_CODE LIKE 'LL%') 
     AND (DESCRIPTION LIKE 'stock%') 
     OR (DESCRIPTION LIKE '%stk count%') 
) 
SELECT 
    STOCK_CODE, 
    TRANS_DATE, 
    STOCK_QTY, 
    DESCRIPTION, 
    DETAIL 
FROM CTE 
WHERE rn = 1 

---

你肯定你的WHERE条款？是不是应该是这样的：

WHERE 
     TRANS_TYPE = N'STADJ' 
     AND STOCK_CODE LIKE 'LL%' 
     AND (
      DESCRIPTION LIKE 'stock%' 
      OR DESCRIPTION LIKE '%stk count%' 
     )