回答

收藏

如何使用GROUP BY子句将查询移植到PostgreSQL?

技术问答 技术问答 210 人阅读 | 0 人回复 | 2023-09-12

我正在移植一个简单的成本数据库Postgres,并被困在使用中GROUPBY和多个JOIN在句子的视图上。Postgres希望我使用GROUP BY所有中的所有表。, H9 p/ r: x' b/ }! f/ A# w
最后定义了表。需要注意的是,列account_id,receiving_account_id并place可能NULL和operation有0个标签。6 R. x9 \8 Z: X. V
原始CREATE声明CREATE VIEW details AS SELECT    op.id,   op.name,   c.name,   CASE --amountsign        WHEN op.receiving_account_id IS NOT NULL THEN            CASE                WHEN op.account_id IS NULL THEN          ELSE '=      END        ELSE '-     END || ' ' || printf("%.2f",op.amount) || ' z艂' AS amount,   CASE --account        WHEN op.receiving_account_id IS NOT NULL THEN            CASE                WHEN op.account_id IS NULL THEN ac2.name                ELSE ac.name || ' -> ' || ac2.name            END        ELSE ac.name    END AS account,    t.name AS type,   CASE --date        WHEN op.time IS NOT NULL THEN op.date || ' ' || op.time        ELSE op.date    END AS date,   p.name AS place,   GROUP_CONCAT(tag.name,',') AS tagsFROM operation opLEFT JOIN category c ON op.category_id = c.idLEFT JOIN type t ON op.type_id = t.idLEFT JOIN account ac ON op.account_id = ac.idLEFT JOIN account ac2 ON op.receiving_account_id = ac2.idLEFT JOIN place p ON op.place_id = p.idLEFT JOIN operation_tag ot ON op.id = ot.operation_idLEFT JOIN tag ON ot.tag_id = tag.idGROUP BY IFNULL (ot.operation_id,op.id)ORDER BY date DESCPostgres当前查询我做了一些更新,目前的说法是:
% S& }* d7 \6 z: M# i0 k0 U! c( ZBEGIN TRANSACTION;CREATE VIEW details AS SELECT    op.id,   op.name,   c.name,   CASE --amountsign        WHEN op.receiving_account_id IS NOT NULL THEN            CASE                WHEN op.account_id IS NULL THEN          ELSE '=      END        ELSE '-     END || ' ' || op.amount || ' z艂' AS amount,   CASE --account        WHEN op.receiving_account_id IS NOT NULL THEN            CASE                WHEN op.account_id IS NULL THEN ac2.name                ELSE ac.name || ' -> ' || ac2.name            END        ELSE ac.name    END AS account,    t.name AS type,   CASE --date        WHEN op.time IS NOT NULL THEN to_char(op.date,'DD.MM.YY') || ' ' || op.time        ELSE to_char(op.date,'DD.MM.YY    END AS date,   p.name AS place,   STRING_AGG(tag.name,',') AS tagsFROM operation opLEFT JOIN category c ON op.category_id = c.idLEFT JOIN type t ON op.type_id = t.idLEFT JOIN account ac ON op.account_id = ac.idLEFT JOIN account ac2 ON op.receiving_account_id = ac2.idLEFT JOIN place p ON op.place_id = p.idLEFT JOIN operation_tag ot ON op.id = ot.operation_idLEFT JOIN tag ON ot.tag_id = tag.idGROUP BY COALESCE (ot.operation_id,op.id)ORDER BY date DESC;COMMIT;在Column 'x' must appear in GROUP BY clause当我添加列出的错误时,我在这里遇到了错误:3 y: t6 `7 ?: l; N
GROUP BY COALESCE(ot.operation_id,op.id),op.id,c.name,ac2.name,ac.name,t.name,p.name当我添加p.name列时,我Column 'p.name' is defined more than once error.如何解决这个问题?! O5 r+ a, D1 ]$ `) h. x. E
表定义CREATE TABLE operation (  id integer NOT NULL PRIMARY KEY, name character varying(64) NOT NULL, category_id integer NOT NULL, type_id integer NOT NULL, amount numeric(8,2) NOT NULL, date date NOT NULL, "time" time without time zone NOT NULL, place_id integer, account_id integer, receiving_account_id integer, CONSTRAINT categories_transactions FOREIGN KEY (category_id)      REFERENCES category (id) MATCH SIMPLE      ON UPDATE NO ACTION ON DELETE NO ACTION,    CONSTRAINT transactions_transaction_types FOREIGN KEY (type_id)      REFERENCES type (id) MATCH SIMPLE      ON UPDATE NO ACTION ON DELETE NO ACTION);                 CONSTRAINT transactions_transaction_types FOREIGN KEY (type_id)      REFERENCES type (id) MATCH SIMPLE      ON UPDATE NO ACTION ON DELETE NO ACTION);               
0 q+ T+ O9 _: t: U# j' L    解决方案:
分享到:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则