CTE 101
Today I first learnt the concept of CTE (Common Table Expression) from my colleague Rod. Basically, CTE can be used to improve the readability of your long and complex SQL statement. for example, without CTE, you might write the following SQL: CREATE OR REPLACE VIEW dfx.test_vw123 AS SELECT a.FIELD_A, a.FIELD_B, b.FIELD_C b.FIELD_D FROM ( SELECT MAX(FIELD_A) AS FIELD_A, COUNT(*) AS FIELD_B FROM TABLE_A ta WHERE ta.FIELD_E = 'General' GROUP BY ta.FIELD_F ) a LEFT JOIN TABLE_A b ON a.FIELD_A = b.FIELD_A with CTE, you could write the following one with better readability. CREATE OR REPLACE VIEW dfx.test_vw123 AS WITH TABLE_A_STATS AS ( SELECT MAX(FIELD_A) AS FIELD_A, COUNT(*) AS FIELD_B FROM TABLE_A ta WHERE ta.FIELD_E = 'General' GROUP BY ta.FIELD_F ) SELECT a.FIELD_A, a.FIELD_B, b.FIELD_C b.FIELD_D FROM TABLE_A_STATS a LEFT JOIN TABLE_A b ON a.FIELD_A = b.FIELD_A; For further information about CTE, click Reference I also found another article about CTE in Chinese.
Permanent link to “CTE 101”
