如何将两列相乘，然后在不反复执行forumla的情况下重用该值

Question

我正在尝试将两列相乘，然后使用该值来执行CASE语句，我真的不知道该如何询问，也不知道是否可能，所以请与我联系。

SELECT (table1.price)*(table2.quantity) AS "total price", CASE WHEN (table1.price)*(table2.quantity) < 10 THEN 3 CASE WHEN (table1.price)*(table2.quantity) > 10 THEN (CASE WHEN (table1.price)*(table2.quantity) < 20 THEN 2 ELSE 0 END) ELSE 0 END
FROM table1 JOIN table2 ON table1.id = table2.itemid 
WHERE table1.id = 41212;

有没有办法将table1.price*table2.quantity作为的函数？我可以将其作为别名并重用该别名吗？我以后也必须在WHERE语句中使用它。

Answer 1

尝试一个子查询：

SELECT tmp."total price", CASE WHEN tmp."total price" < 10 THEN 3 CASE WHEN tmp."total price" > 10 THEN (CASE WHEN tmp."total price" < 20 THEN 2 ELSE 0 END) ELSE 0 END
FROM
(
SELECT (table1.price)*(table2.quantity) AS "total price"
FROM table1
JOIN table2 ON table1.id = table2.itemid 
WHERE table1.id = 41212;
) AS tmp