分析函数是基于最终的结果集进行开窗的,所以HAVING比分析函数先执行
↓ 没有HAVING时,MAX(col3) over()是A2
sql">SQL> WITH subq_a AS2 (SELECT 'A' col1, 'A1' col2, 10 col33 FROM dual4 UNION ALL5 SELECT 'A' col1, 'A1' col2, -5 col36 FROM dual7 UNION ALL8 SELECT 'A' col1, 'A2' col2, 10 col39 FROM dual10 UNION ALL11 SELECT 'A' col1, 'A2' col2, -15 col312 FROM dual)13 SELECT col1, col2, SUM(col3) s_col3, MAX(col2) over() x_col214 FROM subq_a15 GROUP BY col1, col216 /COL1 COL2 S_COL3 X_COL2
---- ---- ---------- ------
A A1 5 A2
A A2 -5 A2
↓ 有HAVING时,分析函数在HAVING执行过滤后的结果集上开窗计算结果,MAX(col3) over()变成A1
sql">SQL> WITH subq_a AS2 (SELECT 'A' col1, 'A1' col2, 10 col33 FROM dual4 UNION ALL5 SELECT 'A' col1, 'A1' col2, -5 col36 FROM dual7 UNION ALL8 SELECT 'A' col1, 'A2' col2, 10 col39 FROM dual10 UNION ALL11 SELECT 'A' col1, 'A2' col2, -15 col312 FROM dual)13 SELECT col1, col2, SUM(col3) s_col3, MAX(col2) over() x_col214 FROM subq_a15 GROUP BY col1, col216 HAVING SUM (col3) > 017 /COL1 COL2 S_COL3 X_COL2
---- ---- ---------- ------
A A1 5 A1