我可以在 CASE 语句 PostgreSQL 中使用 'alias' AS 吗

Question

SELECT 
id
, quantity
, unit_price 
, (antity * unit_price) as price    
CASE
WHEN quantity * unit_price > 5000 THEN (quantity * unit_price) * 0.2
WHEN quantity * unit_price > 3000 THEN (quantity * unit_price) * 0.15 
ELSE null 
END 
AS discount
FROM OrderDetails;

我试图在CASE中使用别名price，但它不起作用。在上面的代码中，我重复了quantity * unit_price5次。有没有更好的方法来实现这段代码?在CASE语句中有避免重复的方法吗?

Answer 1

你可以使用CTE

WITH CTE as (
SELECT 
id
, quantity
, unit_price,
(antity * unit_price) as price
FROM OrderDetails
)
SELECT 
id
, quantity
, unit_price 
, price    
CASE
WHEN price > 5000 THEN price * 0.2
WHEN price > 3000 THEN price * 0.15 
ELSE null 
END 
AS discount
FROM OrderDetails;