请求基于三张关联表创建含指定字段的数据库视图

Got it, let's build the view you need step by step. The key here is to leverage joins between your junction table and the two main tables to pull in the names alongside the IDs you need.

Quick Breakdown of the Approach: #

• We'll link the kpi_per_prog junction table to your program table (I'll assume it's named programs—swap this with your actual "表2" name if it's different) using the shared prog_id
• Then join the result to your KPI table (assumed kpis—replace with your actual "表1" name if needed) via kpi_id
• Alias the ID fields to match your requested id_prog and id_kpi labels
• Since prog_id and kpi_id form a composite primary key on kpi_per_prog, this pair will remain unique in the resulting view

SQL Code for the View: #

CREATE VIEW kpi_program_associations AS
SELECT
    kpp.prog_id AS id_prog,
    kpp.kpi_id AS id_kpi,
    p.prog_name,
    k.kpi_name
FROM
    kpi_per_prog kpp
INNER JOIN
    programs p ON kpp.prog_id = p.prog_id
INNER JOIN
    kpis k ON kpp.kpi_id = k.kpi_id;

Quick Notes: #

• If you want to include programs or KPIs that don't have any associations (i.e., no entry in kpi_per_prog), swap INNER JOIN with LEFT JOIN
• Make sure to replace programs and kpis with your actual table names if they differ from what I used

内容的提问来源于stack exchange，提问作者saad