我目前有一个联接查询,用于构建一个视图,该视图仅返回最近一个月中创建的记录,如下所示:
CREATE VIEW [dbo].[MostRecentMonth] AS
SELECT <fields>
FROM dbo.CensusFile
INNER JOIN (
SELECT MAX(CENSUS_MONTH) as MAXMONTH FROM dbo.CensusFile
)
AS child ON (dbo.CensusFile.CENSUS_MONTH = child.MAXMONTH)
GO
查询返回我期望的结果,但正在寻找一种更优化的方法来构建查询,以便在大型数据集上更有效地运行。任何建议表示赞赏。
为了可读性,我将标准移动到它所属的WHERE
子句:
CREATE VIEW dbo.MostRecentMonth AS
SELECT <fields>
FROM dbo.CensusFile
WHERE census_month = (SELECT MAX(census_month) FROM dbo.CensusFile);
另一种选择是窗口函数,但我认为它不会更快,而且可读性更低。
CREATE VIEW dbo.MostRecentMonth AS
SELECT <fields>
FROM
(
SELECT <fields>, MAX(census_month) OVER () AS maxmonth
FROM dbo.CensusFile
) with_maxmonth
WHERE census_month = maxmonth;
对于任一查询,最重要的是您在census_month
上有一个索引:
CREATE INDEX idx ON MostRecentMonth(census_month);