只显示最近的日期的记录
问题描述:
我有一个查询,它返回关于股票的信息,查询的作品,但你可以在下面的图片中看到有一些重复。我希望它在图像中显示,但不是重复我只希望它显示具有最新日期的行。只显示最近的日期的记录
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
答
你可以通过做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%'
)
该查询不起作用SQL Magement Studio抛出错误,我试图通过这种方式使视图。 – SnakeByte 2015-02-06 01:28:04
什么是错误? – 2015-02-06 01:29:07
它说,查询不能用图形表示,我认为这是因为我想制作一个视图,这将显示在Excel电子表格中。 – SnakeByte 2015-02-06 01:32:03