如何使用 sql 选择最近一年的记录

Question

我正在使用SQL Server。我想列出每周的教程信息。

SELECT 
       tx.Lab, t.weekNo 
FROM 
       TutorialX tx 
INNER JOIN 
       Tutorial t 
ON 
       tx.id = t.id 
WHERE 
       t.tutID = @tutID AND 
       t.ProgramID = @pID AND 
       tx.Culture = @culture      
ORDER BY 
       t.WeekNo

年份字段在表Tutorial中。

这个查询为我带来了所有年份的记录。每年有14~16个记录。每年都会有14~16个新的纪录，但我只想要最大/最近的纪录。今天，我想拿到2012年的14~16张唱片，明年是2013年的……

我已经尝试过了，但是使用这个查询的结果是相同的:

SELECT 
       tx.Lab, t.weekNo 
FROM 
       TutorialX tx 
INNER JOIN 
       Tutorial t 
ON 
       tx.id = t.id 
WHERE 
       t.tutID = @tutID AND 
       t.ProgramID = @pID AND 
       tx.Culture = @culture AND
       t.year IN ( SELECT MAX(year) AS y FROM Tutorial GROUP BY year)
ORDER BY 
       t.WeekNo

Answer 1

如果您使用的是SQL Server 2005+，您可以使用ROW_NUMBER() OVER(PARTITION BY ...)排序函数:

WITH CTE
AS
(
    SELECT  tx.Lab,
      ROW_NUMBER() OVER(PARTITION BY year ORDER BY t.weekno) AS RN 
    FROM TutorialX tx 
    INNER JOIN Tutorial t ON tx.id = t.id 
    WHERE t.tutID = @tutID 
      AND t.ProgramID = @pID 
      AND tx.Culture = @culture      
)
SELECT Lab
FROM CTE
WHERE RN = 1;

Answer 2

我已经解决了内部查询中删除GROUP BY year部分的问题。

SELECT 
    t.weekNo, tx.Lab 
FROM 
    TutorialX tx 
INNER JOIN 
    Tutorial t 
ON 
    tx.id = t.id 
WHERE 
    t.tutID = @tid AND 
    t.ProgramID = @pid AND 
    tx.Culture = @culture AND 
    t.year IN (SELECT MAX(year) AS y FROM Tutorial) 
ORDER BY 
    t.weekNo