我正在学习使用SQL Server,使用SQL Server Management Studio。以下是产生错误的代码:
SELECT SalesPersonID, COUNT(SalesOrderID), YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
GROUP BY SalesYear;
为什么会出现这个错误?
“SalesYear”列名无效。
我正在学习使用SQL Server,使用SQL Server Management Studio。以下是产生错误的代码:
SELECT SalesPersonID, COUNT(SalesOrderID), YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
GROUP BY SalesYear;
Sales year
为别名,根据逻辑查询处理模型,以下是按顺序执行的运算符..1 FROM
2 WHERE
3 GROUP BY
4 HAVING
5 SELECT
5.1 SELECT list
5.2 DISTINCT
6 ORDER BY
7 TOP / OFFSET-FETCH
无效的列名“SalesYear”。
在表SalesOrderHeader
中不会有这一列。
SELECT SalesPersonID, COUNT(SalesOrderID), YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader GROUP BY YEAR(OrderDate)
DECLARE @SalesOrderHeader TABLE(SalesPersonID INT,SalesOrderID INT,OrderDate DATETIME)
INSERT INTO @SalesOrderHeader
SELECT 1,101,'20-Mar-2018' UNION ALL SELECT 1,102,'21-Mar-2018' UNION ALL SELECT 1,102,'21-Mar-2018' UNION ALL SELECT 2,202,'21-Mar-2018'
SELECT * FROM @SalesOrderHeader
SELECT SalesPersonID AS SalesPersonID, COUNT(SalesOrderID) NoOfOrder, YEAR(OrderDate) AS SalesYear
FROM @SalesOrderHeader
GROUP BY SalesPersonID,YEAR(OrderDate);
谢谢。
GROUP BY
在SELECT
之前被评估。因此别名SalesYear
对GROUP BY
不可用。您必须使用GROUP BY YEAR(OrderDate)
。 - Giorgos Betsos