sql - 计算与约束有关的总和

Question

我正在使用 PostgreSQL 8.4。

我有以下 sql 查询:

SELECT p.partner_id,
CASE WHEN pa.currency_id = 1 THEN SUM(amount) ELSE 0 END AS curUsdAmount,
CASE WHEN pa.currency_id = 2 THEN SUM(amount) ELSE 0 END AS curRubAmount,
CASE WHEN pa.currency_id = 3 THEN SUM(amount) ELSE 0 END AS curUahAmount
FROM public.player_account AS pa
JOIN player AS p ON p.id = pa.player_id
WHERE p.partner_id IN (819)
GROUP BY p.partner_id, pa.currency_id

问题是查询不符合我的预期。我意识到这一点，但现在我想了解该查询到底做了什么。我的意思是，SUM 将在查询执行后计算。你能澄清一下吗？

Answer 1

我认为您在查询中有倒退的条件:

SELECT p.partner_id,
       SUM(CASE WHEN pa.currency_id = 1 THEN amount ELSE 0 END) AS curUsdAmount,
       SUM(CASE WHEN pa.currency_id = 2 THEN amount ELSE 0 END) AS curRubAmount,
       SUM(CASE WHEN pa.currency_id = 3 THEN amount ELSE 0 END) AS curUahAmount
FROM public.player_account pa JOIN
     player p
     ON p.id = pa.player_id
WHERE p.partner_id IN (819)
GROUP BY p.partner_id;

请注意，我还从 group by 子句中删除了 currency_id。