SQL聚集语法规则
1. 聚集语法规则
聚集的语法规则各数据库有差异。
2. 投影列规则
在有Group By的情况下,投影列要么出现在Group By中,要么在聚集函数中,例如,以下SQL是合法的:
- snippet.sql
SELECT SUM(a) FROM t GROUP BY a; SELECT SUM(a),a FROM t GROUP BY a; SELECT SUM(b),a FROM t GROUP BY a; SELECT SUM(a+b) FROM t GROUP BY a+b; SELECT SUM(a+b),a+b FROM t GROUP BY a+b; SELECT a+b FROM t GROUP BY a+b; SELECT a,b FROM t GROUP BY a,b;
以下SQL是非合法的:
- snippet.sql
-- 投影列b既不在聚集函数中,也不在Group By中: SELECT SUM(a),b FROM t GROUP BY a; SELECT * FROM t GROUP BY a; SELECT b FROM t GROUP BY a; SELECT a,b FROM t GROUP BY a; SELECT SUM(a+b),a FROM t GROUP BY a+b;
有些数据库,支持上述非法SQL,例如,
- snippet.sql
SELECT b FROM t GROUP BY a;
因为a的每个分组中可能对应多个b值,返回结果b列显示第一个值。
打赏作者以资鼓励: