Help Center> Data Warehouse Service (DWS)> Tool Guide> DSC> SQL Syntax Migration Reference> MySQL Syntax Migrating> Schema Objects and Data Definition Language (DDL)> Table (Optional Parameters and Operations)> WITH AS
Updated on 2024-04-28 GMT+08:00
View PDF

WITH AS

WITH AS is used in GaussDB(DWS) to declare one or more subqueries that can be referenced by name in the main query. It is equivalent to a temporary table. DSC supports this keyword, which is retained in the migration.

Input

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
WITH e AS (
  SELECT city, sum(population) FROM t1 group by city
  ),
  d AS (
  SELECT max(music) as max_music, min(music) as min_music from student
  ),
  s AS (
  SELECT * FROM subject
  )
SELECT * FROM e;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
WITH e AS (
  SELECT
    city, sum(population)
  FROM
    t1
  GROUP BY
    city
),
d AS (
  SELECT
    max(music) AS "max_music", min(music) AS "min_music"
  FROM
    student
),
s AS (
  SELECT
    *
  FROM
    subject
)
SELECT
  *
FROM
  e;