SQL 无效列名问题-解网

问：

我收到无效的列名错误，我不明白为什么......

SELECT TOP (1000) i.[t207f005_classification_code]
      ,i.[t207f010_sort_key]
      ,i.[t207f015_date_effective]
      ,i.[t207f020_rate]
      ,i.[t207f025_annual_rate]
      ,i.[t207f030_rate_high]
      ,i.[t207f035_annual_rate_high]
      ,i.[DEXLastUpdateDt]
      ,RANK () OVER ( 
            PARTITION BY i.[t207f005_classification_code]
            ORDER BY i.[t207f015_date_effective] ASC
        ) AS ClassificationRank
  FROM [DEX].[HrPayroll].[t207_classification_rate] i
  Left join 
  (SELECT [t207f005_classification_code]
          ,[t207f015_date_effective] - 1 as Date_To_Derived
          ,[t207f020_rate]
      ,RANK () OVER ( 
            PARTITION BY [t207f005_classification_code]
            ORDER BY [t207f015_date_effective] ASC
        ) - 1 AS NextClassificationRank
  FROM 
    [DEX].[HrPayroll].[t207_classification_rate]) h
  ON h.[t207f005_classification_code] = i.[t207f005_classification_code]
  AND ClassificationRank = h.NextClassificationRank

What I am trying to achieve is a table that just outputs:

i.[t207_classification_rate], 
i.[t207f015_date_effective], 
h.[Date_To_Derived], 
i.[t207f020_rate]

实际上，每个分类的费率所针对的日期范围。

在谷歌上搜索答案并请同事帮忙。无法找到解决方案。

sql sql-server sql-server-2008-r2

SELECT *
FROM 
(
    SELECT TOP (1000) [t207f005_classification_code]
                     ,[t207f010_sort_key]
                     ,[t207f015_date_effective]
                     ,[t207f020_rate]
                     ,[t207f025_annual_rate]
                     ,[t207f030_rate_high]
                     ,[t207f035_annual_rate_high]
                     ,[DEXLastUpdateDt]
                     ,RANK () OVER (PARTITION BY [t207f005_classification_code] ORDER BY [t207f015_date_effective] ASC) AS ClassificationRank
    FROM [DEX].[HrPayroll].[t207_classification_rate]
    ORDER BY ClassificationRank
) I
LEFT JOIN 
(
    SELECT [t207f005_classification_code]
          ,[t207f015_date_effective]
          ,[t207f020_rate]
          ,RANK () OVER ( PARTITION BY [t207f005_classification_code] ORDER BY [t207f015_date_effective] ASC) - 1 AS NextClassificationRank
   FROM [DEX].[HrPayroll].[t207_classification_rate]
) H
    ON I.[t207f005_classification_code] = H.[t207f005_classification_code]
    AND I.ClassificationRank = H.NextClassificationRank;

此外，您可以使用 LEAD 和 LAG 来获取上一个和下一个值。无需使用子查询。检查一下：

SELECT TOP (1000) [t207f005_classification_code]
                 ,[t207f010_sort_key]
                 ,[t207f015_date_effective]
                 ,[t207f020_rate]
                 ,[t207f025_annual_rate]
                 ,[t207f030_rate_high]
                 ,[t207f035_annual_rate_high]
                 ,[DEXLastUpdateDt]
                 ,RANK () OVER (PARTITION BY [t207f005_classification_code] ORDER BY [t207f015_date_effective] ASC) AS ClassificationRank
                 --
                 ,LEAD([t207f005_classification_code]) OVER (PARTITION BY [t207f005_classification_code] ORDER BY [t207f015_date_effective] ASC)
                 ,LEAD([t207f015_date_effective]) OVER (PARTITION BY [t207f005_classification_code] ORDER BY [t207f015_date_effective] ASC)
                 ,LEAD([t207f020_rate]) OVER (PARTITION BY [t207f005_classification_code] ORDER BY [t207f015_date_effective] ASC)
                 --
                 ,LAG([t207f005_classification_code]) OVER (PARTITION BY [t207f005_classification_code] ORDER BY [t207f015_date_effective] ASC)
                 ,LAG([t207f015_date_effective]) OVER (PARTITION BY [t207f005_classification_code] ORDER BY [t207f015_date_effective] ASC)
                 ,LAG([t207f020_rate]) OVER (PARTITION BY [t207f005_classification_code] ORDER BY [t207f015_date_effective] ASC)
FROM [DEX].[HrPayroll].[t207_classification_rate]
ORDER BY ClassificationRank

啊对了。谢谢。在版本 Microsoft SQL Server 2008 R2 （SP3-GDR）（KB4057113） - 10.50.6560.0 （x64）上 2017 年 12 月 28 日 15：03：48 版权所有（c） Windows NT 6.1 <X64>（内部版本 7601：Service Pack 1）（虚拟机监控程序）上的 Microsoft Corporation Enterprise Edition（64 位）。

0赞 Carl Blunck 2/28/2023

更新了我的初始帖子以显示我想要获得的输出，最初并不完整。不好意思。

0赞 gotqn 2/28/2023

@CarlBlunck，如果第一个查询正常工作，则只需添加所需的列即可。

上一个：对文件流 blob 表具有 where 条件的查询，从不仅在某些服务器上返回

下一个：如何使用单个存储过程在 ASP.NET MVC DAL类中分配SQL@Action标志？

SQL 无效列名问题

SQL Invalid Column Name Issue

评论

评论