我们有一个查询,希望返回2个最新的按日期记录。
例如:
Code Item Date Price1 Price2
--------------------------------------------------------------
0000 10000 2020-04-10 00:00:00.000 55.000000 55.000000
0000 10000 2020-04-04 00:00:00.000 55.000000 55.000000
0000 10000 2020-03-07 00:00:00.000 55.000000 55.000000
0000 10000 2020-03-04 00:00:00.000 55.000000 55.000000
0000 10000 2020-02-04 00:00:00.000 48.000000 48.000000
到目前为止编写的SQL语法,通过两个表的内部联接,按DESC
返回并排序记录,但我们需要获得最近的两个记录,参数为日期和记录条目。
SELECT
t0.Code, t1.Item, t0.Date,
t1.Price AS price1, t1.Price AS price2
FROM
dbo.INV AS t0
INNER JOIN
dbo.INV1 AS t1 ON t0.RecEntry = t1.RecEntry
WHERE
(t1.Price <> 0)
ORDER BY
t1.Date DESC
例如,使用SELECT TOP * 2
,after by不起作用,可能是由于语法原因,因为我们以前从未编写过类似的查询。
如有任何提示,不胜感激!
谢谢。
试试这个
SELECT top 2
t0.Code, t1.Item, t0.Date,
t1.Price AS price1, t1.Price AS price2
FROM
dbo.INV t0
INNER JOIN
dbo.INV1 t1 ON t0.RecEntry = t1.RecEntry
WHERE
(t1.Price <> 0)
ORDER BY
t1.Date DESC