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

张洪斌

我正在移植一个简单的成本数据库Postgres，并被困在使用中GROUPBY和多个JOIN在句子的视图上。Postgres希望我使用GROUP BY所有中的所有表。
! N* r1 @, K0 y8 f0 D最后定义了表。需要注意的是，列account_id，receiving_account_id并place可能NULL和operation有0个标签。
原始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当前查询我做了一些更新，目前的说法是:
1 d* ]; V4 |+ u# YBEGIN 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 z  t6 c4 t" F, AGROUP 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.如何解决这个问题？
* [7 I% R% {7 N2 x- v6 P, S) G表定义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);               
- c. f2 a2 [5 y- `& l) b6 O    解决方案: