#sql # 1. Test - 表结构： ```sql -- create table create table t(a bigint); insert into t values(1),(2),(3); ``` - 数据库版本: - **Mysql**：8.0.36-0ubuntu0.22.04.1 - **DuckDB**: v0.9.3-dev3269 8de2d291d8d76a715d21011deb1500d19ecb7bd9 - **PostgreSQL**: PostgreSQL 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit - **SparkSQL**: 3.5.0 ce5ddad990373636e94071e7cef2f31021add07b ## 1.1 从 from/input 中引用 ```sql select a+1 from t group by c; select a+1 from t group by c+1; ``` - 列名和包含表达式的列名都可以 ## 1.2 从 selection 中引用 ### groupby 列名：都允许 ```sql select 0 as c from t group by c; ``` - 都允许 ### groupby 表达式 ```sql -- 表达式中的列引用自 select list select 0 as c from t group by c+1; ``` - **postgresql**: ERROR: column "c" does not exist - **duckdb**: Error: Binder Error: Referenced column "c" not found in FROM clause! Candidate bindings: "t.a" - **mysql**: 允许 - **spark**: 允许 ## 1.3 引用有歧义 ```sql -- from 和 selection/ SELECT list 中都有列 a select 0 as a from t group by a; ``` - from 中优先级高 - mysql 报 wanning：Column 'a' in group statement is ambiguous - `select 0 as a from t group by a + 1` 不会报 warnning # 2. Doc ## PostgresSQL - https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY > An _`expression`_ used inside a _`grouping_element`_ can be an input column name, or the name or ordinal number of an output column (`SELECT` list item), or an arbitrary expression formed from input-column values. In case of **ambiguity**, a `GROUP BY` name will be interpreted as an input-column name rather than an output column name.