就像已经提供的@Andomar一样:大多数RDBMS要求按未聚合的每一列进行分组- 查询中的其他任何位置(包括SELECT
列表,还包括WHERE
子句等)。
因此op.id
覆盖了整个表格,这应该适用于您当前的查询:
GROUP BY op.id, c.name, 5, t.name, p.name
5
作为一个 的SELECT
列表,这也允许在Postgres的。这只是重复长表达的简写形式:
CASE
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
我从您的名字得出,您与之间有一个:m关系,operation
并通过tag
实现operation_tag
。所有其他联接似乎都没有将行相乘,因此单独聚合标签会更有效- 就像@Andomar暗示的那样,只需弄清楚逻辑即可。
这应该工作:
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
, **to_char(op.date, 'DD.MM.YY') || ' ' || op.time AS date** -- see below
, p.name AS place
, ot.tags
FROM operation op
LEFT JOIN category c ON op.category_id = c.id
LEFT JOIN type t ON op.type_id = t.id
LEFT JOIN account ac ON op.account_id = ac.id
LEFT JOIN account ac2 ON op.receiving_account_id = ac2.id
LEFT JOIN place p ON op.place_id = p.id
**LEFT JOIN (
SELECT operation_id, string_agg(t.name, ', ') AS tags
FROM operation_tag ot
LEFT JOIN tag t ON t.id = ot.tag_id
GROUP BY 1
) ot ON op.id = ot.operation_id**
**ORDER BY op.date DESC, op.time DESC** ;