SQL - 在Group By中使用别名 [英] SQL - using alias in Group By

问题描述

只是对SQL语法感到好奇。因此，如果我有

  SELECT 
 itemName作为ItemName，
 substring（itemName，1,1）as FirstLetter，
 Count（itemName）
 FROM table1 
 GROUP BY itemName，FirstLetter 
  

这是不正确的，因为

  GROUP BY itemName，FirstLetter 
  

确实应该是

  GROUP BY itemName，substring（ itemName，1,1）
  

但为什么我们不能简单地使用前者来方便呢？

解决方案

SQL的实现与查询按以下顺序执行一样：

1. FROM子句


2. WHERE子句


3. GROUP BY子句
   
4. HAVING子句
   
5. SELECT子句


6. ORDER BY子句

对于大多数关系数据库系统，此顺序解释哪些名称（列或别名）是有效的，因为它们必须具有b因此在Oracle和SQL Server中，您不能在SELECT子句中定义的GROUP BY子句中使用一个术语，因为GROUP BY是在SELECT子句之前执行的。

虽然有例外：MySQL和Postgres似乎有更多的智能，允许它。

Just curious about SQL syntax. So if I have

SELECT 
 itemName as ItemName,
 substring(itemName, 1,1) as FirstLetter,
 Count(itemName)
FROM table1
GROUP BY itemName, FirstLetter

This would be incorrect because

GROUP BY itemName, FirstLetter 

really should be

GROUP BY itemName, substring(itemName, 1,1)

But why can't we simply use the former for convenience?

解决方案

SQL is implemented as if a query was executed in the following order:

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

For most relational database systems, this order explains which names (columns or aliases) are valid because they must have been introduced in a previous step.

So in Oracle and SQL Server, you cannot use a term in the GROUP BY clause that you define in the SELECT clause because the GROUP BY is executed before the SELECT clause.

There are exceptions though: MySQL and Postgres seem to have additional smartness that allows it.

这篇关于SQL - 在Group By中使用别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！