|
我正在移植一个简单的成本数据库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 解决方案: |
|