Help Center/ GaussDB(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-07-19 GMT+08:00
View PDF
Share

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;