时间戳

首页 > 解决方案 > 在 PIVOT SQL 之后未运行第二个选择查询

sql - 在 PIVOT SQL 之后未运行第二个选择查询

问题描述

所以基本上我有一个类似于这个的代码来执行存储过程：

DROP TABLE IF EXISTS [TempTable]

SELECT [Item1], [Item2], [Item3] 
INTO [TempTable]
FROM [Table1] K 
INNER JOIN [Table2] C
WHERE [Item3] = @timeVariable
GROUP BY [Item1];

SELECT [Item1], [Item2], [Item3], [Item2]/[Item3] AS Sum1
FROM [TempTable];
GO

问题是，当我尝试运行第二个查询时，它在列中显示错误。

如果我单独运行代码，它不会发生。

例如：

  1. 先跑上半部分
DROP TABLE IF EXISTS [TempTable]

SELECT [Item1], [Item2], [Item3] 
INTO [TempTable]
FROM [Table1] K 
INNER JOIN [Table2] C
WHERE [Item3] = @timeVariable
GROUP BY [Item1];
  1. 然后整个代码
DROP TABLE IF EXISTS [TempTable]

SELECT [Item1], [Item2], [Item3] 
INTO [TempTable]
FROM [Table1] K 
INNER JOIN [Table2] C
WHERE [Item3] = @timeVariable
GROUP BY [Item1];

SELECT [Item1], [Item2], [Item3], [Item2]/[Item3] AS Sum1
FROM [TempTable];
GO

我正在尝试找到一种方法来成功运行整个查询，而不会在第二个查询的第一次运行中出错并且必须单独运行查询。

错误是

列名“列名”无效

标签： sqlsql-serverselectpivot

解决方案

You have use **GROUP BY [Item1]**
Group by user for aggregate like Sum, Avg, Count etc.
If you use Group by then your code should be like below:

DROP TABLE IF EXISTS [TempTable]

SELECT [Item1], SUM([Item2])[Item2], SUM([Item3])Item3
INTO [TempTable]
FROM [Table1] K 
INNER JOIN [Table2] C ON K.[Join Column] = C.[Join Column]
WHERE [Item3] = @timeVariable
GROUP BY [Item1];

SELECT [Item1], [Item2], [Item3], [Item2]/[Item3] AS Sum1
FROM [TempTable]; 
GO

//Here Join Column would be your primary key and foreign key

推荐阅读