允许 SQL 相同的列名 [英] SQL same column name allowed

问题描述

当我在 SQL Server 中运行此查询时

When I run this query in SQL Server

SELECT custid AAA, companyname AAA
FROM Sales.Customers
WHERE country = 'USA'

运行良好.但是现在结果集有重复的列名(AAA).为什么 SQL Server 允许这种情况发生?如果需要引用列名，应用程序如何工作?我知道如果你把这个查询作为派生表 SQL 会阻止你.喜欢

It's running fine. But now the result set have duplicate column name(AAA). Why SQL server allow this to happen? How an application works if it needs to reference the column name? I know if you put this query as a derived table SQL will stop you. like

SELECT * 
FROM 
     (SELECT custid AAA, companyname AAA
      FROM Sales.Customers
      WHERE country = 'USA') BBB

SQL Server 报错:

SQL Server reports an error:

为BBB"多次指定了AAA"列

The column 'AAA' was specified multiple times for 'BBB'

这背后的逻辑是什么?

谢谢

推荐答案

这可以通过理解查询执行的不同逻辑阶段的执行顺序来解释.查询执行订单

This can be explained by understanding order of execution of different logical phases of query execution. Query Execution Order MSDN

在 SQL Server 中，顺序是 FROM >哪里 >SELECT 即执行第一个 FROM 子句，然后执行 WHERE 子句，最后一个是 SELECT 列表.

In SQL Server the order is FROM > WHERE > SELECT i.e. first FROM clause is executed then WHERE clause and last is the SELECT list.

现在在您的第一个查询中，从表 Sales.Customers 中获取所有匹配的行，然后在 SELECT 列表中指定的列被拉出，然后应用别名.

Now in your first query , all matching rows from table Sales.Customers are fetched and then then afterwards columns specified in SELECT list are pulled out and then Alias names are applied.

在您的第二个查询中，内部查询作为第一个查询成功执行，但是当外部查询的 FROM 子句尝试从内部查询返回的结果集中获取列时，它发现重复的列并抛出错误.

In your second query , the inner query is executed successfully as the first query but when the outer query's FROM clause tries to fetch columns from resultset returned by inner query , it finds duplicate columns and throws error.

这篇关于允许 SQL 相同的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！