資料庫操作思維 - SQL 使用 - 實體工作坊第三節課

[Fionn88]

-- CTE
WITH onsite_course AS (
    SELECT
        *
    FROM
        courses
    WHERE
        place = '現場'
),
onsite_course_contest AS (
    SELECT 
        *
    FROM
        contests
    WHERE
        course_id IN (
            SELECT
                id
            FROM
                onsite_course
        )
)
SELECT
    *
FROM
    questions
WHERE
    contest_id IN (
        SELECT
            id
        FROM
            onsite_course_contest
    )

[Fionn88]

-- CTE 改用 JOIN 替代 subquery
WITH onsite_course AS (
    SELECT
        *
    FROM
        courses
    WHERE
        place = '現場'
),
onsite_course_contest AS (
    SELECT 
        contests.*
    FROM
        contests
    LEFT JOIN
        onsite_course
    ON
        contests.course_id = onsite_course.id
    WHERE
        onsite_course IS NOT NULL
)
SELECT
    questions.*
FROM
    questions
INNER JOIN
    onsite_course_contest
ON
    questions.contest_id = onsite_course_contest.id

[Fionn88]

--【小試身手3.3】專案成果展
WITH dim_avg AS (
    SELECT
        project_id,
        AVG(completeness) AS avg_comp,
        AVG(innovativeness) AS avg_inno,
        AVG(technique) AS avg_tech,
        AVG(practicality) AS avg_prac,
        AVG(visualization) AS avg_visu
    FROM
        project_scores
    GROUP BY
        project_id
), 
project_performance AS (
    SELECT
        projects.name AS project_name,
        courses.name AS course_name,
        dim_avg.*,
        ROUND((avg_comp+avg_inno+avg_tech+avg_prac+avg_visu)/5, 2) AS avg_total
    FROM
        dim_avg
    INNER JOIN
        projects
    ON
        dim_avg.project_id = projects.id
    INNER JOIN
        courses
    ON
        projects.course_id = courses.id
),
project_leaders AS (
    SELECT 
        project_id,
        user_id
    FROM
        project_members
    WHERE
        is_leader = TRUE
),
leader_enriched AS (
    SELECT
        project_leaders.project_id,
        users.name AS leader_name
    FROM
        project_leaders
    INNER JOIN
        users
    ON
        project_leaders.user_id = users.id
)
SELECT
    p.project_name,
    p.course_name,
    l.leader_name,
    p.avg_comp,
    p.avg_inno,
    p.avg_tech,
    p.avg_prac,
    p.avg_visu,
    p.avg_total
FROM
    project_performance AS p
LEFT JOIN
    leader_enriched AS l
ON
    p.project_id = l.project_id
;