PostgreSQL不能在CASE语句中使用列别名

Question

不能在CASE子句中使用列别名:

SELECT 
((SELECT SUM(t2.amount) FROM trans t2 WHERE u.mail = t2.paid_to)-
(SELECT SUM(t2.amount) FROM trans t2 WHERE u.mail = t2.paid_by)) AS "balance",
(CASE WHEN balance < u.credit_limit THEN 'YES' ELSE 'NO' END) AS "result"

FROM user u 
LEFT JOIN trans t ON u.mail = t.paid_to OR u.mail = t.paid_by

我该如何解决这个问题?

另一个问题，我应该为内部选择(t2)使用不同的别名，还是我也可以使用相同的别名(t)作为外部选择的trans表?

Answer 1

在CASE语句中使用别名"balance"是有问题的，因为你试图在同一个查询中使用别名作为字段，而Postgresql限制了它。

你可以试着写一个子查询。由于没有提供DB模式，我无法测试这个查询:

WITH t AS (
SELECT 
((SELECT SUM(amount) FROM trans WHERE u.mail = paid_to)-
(SELECT SUM(amount) FROM trans WHERE u.mail = paid_by)) AS balance,
u.credit_limit 
FROM user u 
LEFT JOIN trans t ON u.mail = t.paid_to OR u.mail = t.paid_by
)
SELECT t.balance, 
CASE WHEN t.balance < t.credit_limit THEN 'YES' ELSE 'NO' END AS result
FROM t;