SQL Server Management Studio插入嵌套的select无效列中

Question

由于某种原因，此行上的年份和月份：

(SELECT DENSE_RANK() OVER (ORDER BY [YEAR], [MONTH]), [YEAR] , [MONTH])

突出显示并显示"无效列"。

我做错什么了吗？"yearclass"同时包含这两列。当我从中删除嵌套的select并键入from [dbo].[yearclass]时，它起作用了，所以我认为我的嵌套函数有问题。

INSERT INTO [dbo].[Time] 
SELECT 
DENSE_RANK() OVER (ORDER BY [YEAR], [MONTH]), 
[YEAR], [MONTH] 
FROM
(SELECT DISTINCT [YEAR], [MONTH] 
FROM [dbo].[yearclass]));

Answer 1

我认为正确的子句应该是：

INSERT INTO [dbo].[Time] 
SELECT DENSE_RANK() OVER (ORDER BY [YEAR], [MONTH]) , [YEAR] , [MONTH] 
FROM (SELECT DISTINCT [YEAR], [MONTH] 
FROM [dbo].[yearclass]
) yearmonthclass;

您给我们的查询中有两个sintax错误：

• 一个额外的右括号('('(
• 嵌套选择没有名称

致以最良好的祝愿。

Answer 2

错误的原因是子查询中没有FROM子句。以下代码在MSSQL Server中运行良好。

CREATE TABLE #YearMonth
(
yearvalue int,
monthvalue int
)
INSERT INTO #YearMonth
values(2018,1), (2017,1),(2016,1), (2018,1);

SELECT DENSE_RANK() OVER (ORDER BY Yearvalue,Monthvalue), yearvalue, monthvalue from #YearMonth