T SQL - 相关子查询的雄辩替换 [英] T SQL - Eloquent replacement of Correlated Subquery

问题描述

我有一个查询，目前正在使用相关子查询来返回结果，但我认为这个问题可以更有效地解决，也许使用 ROW_NUMBER().

问题是围绕一个值 v 的配置文件，通过多年的项目.每个项目都有多个版本，每个版本都有自己的配置文件，在版本推出时启动，当前数据如下所示:

<前>ItemId ItemVersionId 年份值============================================1 1 01 0.11 1 02 0.11 1 03 0.21 1 04 0.21 1 05 0.21 1 06 0.31 1 07 0.31 1 08 0.41 2 04 0.31 2 05 0.31 2 06 0.31 2 07 0.41 2 08 0.51 3 07 0.61 3 08 0.72 1 01 0.12 1 01 0.12 1 01 0.2等等

我想在适用的情况下使用最新版本返回项目的完整配置文件.对于上述第 1 项的示例:

<前>ItemId ItemVersionId 年份值============================================1 1 01 0.11 1 02 0.11 1 03 0.21 2 04 0.31 2 05 0.31 2 06 0.31 3 07 0.61 3 08 0.7

我正在使用

SELECT ItemId, ItemVersionId, Year, Value从表 t在哪里项目 ID = 1AND ItemVersionId = (SELECT MAX(ItemVersionId) FROM table WHERE ItemId = t.ItemId AND Year = t.Year)

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

我使用的是 SQL Server 2005.

提前致谢

解决方案

我会用 CTE 来做:

WITH 结果 AS(SELECT Row_Number() OVER (PARTITION BY ItemId, YearORDER BY ItemversionId DESC) AS RowNumber,项目编号,项目版本号，年，价值发件人表)选择项目 ID,项目版本号，年，价值从结果WHERE RowNumber = 1ORDER BY ItemId, Year

I have a query that is currently using a correlated subquery to return the results, but I am thinking the problem could be solved more eloquently perhaps using ROW_NUMBER().

The problem is around the profile of a value v, through a number of years for an Item. Each item has a number of versions, each with its own profile whick starts when the version is introduced and the data currently looks like this:

    
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

I want to return the full profile for an Item using the most recent version where applicable. For the above example for item 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

I am currently using

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)   

Whilst this returns the correct I suspect there is a more efficient way to do it, especially when the table gets large.

I am using SQL Server 2005.

Thanks in advance

解决方案

I would do it with a 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

这篇关于T SQL - 相关子查询的雄辩替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！