PostgreSQL-嘗試創建視圖時出現錯誤“42701”-有解無憂

我嘗試創建一個視圖，其中包含來自其他表的計算資料。但是，我收到此錯誤訊息：[42701] ERROR: column "id" specified more than once。

我的 SQL 腳本：

CREATE VIEW dashboard_view AS
   SELECT c.libelle_commune AS "commune",
       count(bv.id) AS "total_bv",
       count(rsb.id) AS "bv_saisis",
       count(bv.id) - count(rsb.id) AS "bv_en_attente",
       count(rsb.id) / count(bv.id) * 100 AS "pourcentage_saisie",
       count(rsb_t.id) AS "bv_transmis_sie2",
       count(rsb_t.id) / count(bv.id) * 100 AS "pourcentage_transmission",
       d.id,
       c.id
   FROM commune "c"
       JOIN departement "d" ON d.id = c.departement_id
       JOIN bureau_de_vote "bv" ON bv.commune_id = c.id
       LEFT JOIN scrutin_bureau "sb" ON sb.bureau_de_vote_id = bv.id
       LEFT JOIN resultat_scrutin_bureau "rsb" ON rsb.scrutin_bureau_id = sb.id
       LEFT JOIN resultat_scrutin_bureau "rsb_t" ON (
           rsb_t.scrutin_bureau_id = sb.id
           AND rsb_t.etat_id = (SELECT id FROM etat WHERE code = 'transmission')
       )
       JOIN election ON sb.election_id = election.id
   GROUP BY c.id, d.id;

uj5u.com熱心網友回復：

您正在選擇

d.id, c.id

兩列都不能出現在具有名稱的視圖中id- 您需要至少為其中一列指定一個別名，可能兩者都帶有一個描述其id代表內容的別名。

uj5u.com熱心網友回復：

由于錯誤指定，您嘗試id在查詢中創建兩個名稱為 name 的列。

具有相同錯誤的不正確視圖創建的非常簡單的示例：

CREATE VIEW a AS
SELECT 1 AS hello, 2 AS hello;

并糾正一個

CREATE VIEW a AS
SELECT 1 AS hello, 2 AS world;

因此，為了修復您的查詢更改

CREATE VIEW dashboard_view AS
   SELECT c.libelle_commune AS "commune",
       count(bv.id) AS "total_bv",
       count(rsb.id) AS "bv_saisis",
       count(bv.id) - count(rsb.id) AS "bv_en_attente",
       count(rsb.id) / count(bv.id) * 100 AS "pourcentage_saisie",
       count(rsb_t.id) AS "bv_transmis_sie2",
       count(rsb_t.id) / count(bv.id) * 100 AS "pourcentage_transmission",
       d.id,
       c.id

進入

CREATE VIEW dashboard_view AS
   SELECT c.libelle_commune AS "commune",
       count(bv.id) AS "total_bv",
       count(rsb.id) AS "bv_saisis",
       count(bv.id) - count(rsb.id) AS "bv_en_attente",
       count(rsb.id) / count(bv.id) * 100 AS "pourcentage_saisie",
       count(rsb_t.id) AS "bv_transmis_sie2",
       count(rsb_t.id) / count(bv.id) * 100 AS "pourcentage_transmission",
       d.id AS departement_id,
       c.id AS commune_id

轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/535288.html

標籤：数据库postgresql

上一篇：在PostgreSQL中將時間戳轉換為本地時間感到困惑

下一篇：回傳關于更新sql的復雜查詢