代码之家 › 专栏 › 技术社区 › jheppinstall

T SQL-关联子查询的雄辩替换

sql-server-2005 sql

jheppinstall · 技术社区 · 16 年前

我有一个查询当前正在使用相关的子查询返回结果,但我认为这个问题可以用row_number()更有效地解决。

这个问题围绕着一个v值的轮廓线,一个项目需要经过几年的时间。每一项都有许多版本,每个版本都有自己的配置文件辉克,当引入版本时,数据当前如下所示:

    
ItemId    ItemVersionId    Year    Value
===========================================
1         1                01      0.1
1         1                02      0.1
1         1                03      0.2
1         1                04      0.2
1         1                05      0.2
1         1                06      0.3
1         1                07      0.3
1         1                08      0.4
1         2                04      0.3
1         2                05      0.3
1         2                06      0.3
1         2                07      0.4
1         2                08      0.5
1         3                07      0.6
1         3                08      0.7
2         1                01      0.1
2         1                01      0.1
2         1                01      0.2
etc

如果适用,我想使用最新版本返回项目的完整配置文件。对于上述项目1的示例:

ItemId    ItemVersionId    Year    Value
===========================================
1         1                01      0.1
1         1                02      0.1
1         1                03      0.2
1         2                04      0.3
1         2                05      0.3
1         2                06      0.3
1         3                07      0.6
1         3                08      0.7

我正在使用

SELECT ItemId, ItemVersionId, Year, Value
FROM table t
WHERE
    ItemId = 1
    AND ItemVersionId = (SELECT MAX(ItemVersionId) FROM table WHERE ItemId = t.ItemId AND Year = t.Year)

虽然这会返回正确的结果,但我怀疑有一种更有效的方法可以做到这一点,特别是当表变大时。

我正在使用SQL Server 2005。

提前谢谢

3 回复 | 直到 6 年前

Tomalak 16 年前

我会用CTE来做:

WITH Result AS
(
  SELECT Row_Number() OVER (PARTITION BY ItemId, Year
ORDER BY ItemversionId DESC) AS RowNumber
      ,ItemId
      ,ItemversionId
      ,Year
      ,Value
  FROM table
)
SELECT ItemId
  ,ItemversionId
  ,Year
  ,Value
FROM Result
WHERE RowNumber = 1
ORDER BY ItemId, Year

splattne 16 年前

我想你怎么做没关系。你可以检查一下 项目ID和年份的复合索引 .

您可以检查查询计划以查看该查询的影响。

如果数据库中有一个“item”表,您可以尝试另一种方法。 插入列itemversionID 在该表中,并确保在保存新版本时更新该值。然后在你的查询中 使用itemID和itemVersionID联接项目表 而不是使用该子查询。

Tom H 16 年前

虽然您必须使用自己的数据测试性能,但这应该是有效的:

SELECT
    T1.ItemID,
    T1.ItemVersionID,
    T1.Year,
    T1.Value
FROM
    MyTable T1
INNER JOIN (SELECT Year, MAX(ItemVersionID) AS MaxItemVersionID FROM MyTable T2 WHERE T2.ItemID = 1 GROUP BY Year) SQ ON
    SQ.Year = T1.Year AND
    SQ.MaxItemVersionID = T1.ItemVersionID
WHERE
    T1.ItemID = 1

此外,还可以将子查询更改为group by并返回itemID,这样,如果需要为应用程序的其他部分返回数据,则可以一次返回多个项的数据。只需确保然后将itemID添加到联接条件中。