|
我正在移植一个简单的成本数据库Postgres,并被困在使用中GROUPBY和多个JOIN在句子的视图上。Postgres希望我使用GROUP BY所有中的所有表。
! N* r1 @, K0 y8 f0 D最后定义了表。需要注意的是,列account_id,receiving_account_id并place可能NULL和operation有0个标签。! b+ T$ ?! C* q. D- |' y. K
原始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 解决方案: |
|