PostgreSQL -尝试创建视图时出现错误“42701”

aij0ehis 于 2022-11-23 发布在 PostgreSQL

关注(0)|答案(2)|浏览(310)

我试图创建一个包含来自其他表的计算数据的视图。但是，我收到以下错误消息：[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;

postgresql

来源：https://stackoverflow.com/questions/74458917/postgresql-error-42701-when-try-to-create-view

2条答案

按热度按时间

qvk1mo1f1#

您正在选择

d.id, c.id

这两列不能同时出现在名为id的视图中-您需要至少为其中一列提供别名，可能两列都使用描述id所代表内容的别名。

赞(0）回复(0）举报 2022-11-23

8yoxcaq72#

由于错误指定，您尝试在查询中创建两个名为id列
一个非常简单的错误视图创建示例，具有相同的错误：

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

赞(0）回复(0）举报 2022-11-23

我来回答

PostgreSQL -尝试创建视图时出现错误“42701”

2条答案

相关问题

热门标签

最新问答