Postgresql CASE 语句 - 我可以在我的 SELECT 中使用 CASE 的返回值吗？

Postgresql CASE statement - can I use the return value of the CASE within my SELECT?

提问by rishijd

I have a products database with multiple tables to store pricing (because of default pricing and optional override pricing per product colour), and I have CASE statements in my query to get the * Original price of the product * Sale price of the product, if it is on sale

我有一个包含多个表的产品数据库来存储定价（因为默认定价和每种产品颜色的可选覆盖定价），并且我的查询中有 CASE 语句来获取 * 产品的原始价格 * 产品的销售价格，如果有售

I also need to calculate the DISCOUNT of the product if it is on sale. Before I try this, please see the breakdown of my existing SQL which works.

如果产品在售，我还需要计算产品的折扣。在我尝试这个之前，请查看我现有的 SQL 的细分。

SELECT  p.productid, p.stylename,
CASE    WHEN (ppo.original_price IS NOT NULL) THEN ppo.original_price ELSE pp.original_price END AS final_original_price,
CASE    WHEN (ppo.original_price IS NOT NULL) THEN ppo.sale_price ELSE pp.sale_price END AS final_sale_price

FROM product p, ... etc.

The above code works (I have simplified it), and basically, the original price of the product is stored in the column alias "final_original_price", and the sale price (which might be NULL) is returned as "final_sale_price".

上面的代码有效（我已经简化了它），基本上，产品的原始价格存储在列别名“final_original_price”中，销售价格（可能为 NULL）作为“final_sale_price”返回。

I now want to add an additional line to the SELECT to get the discount. I can't use the existing fields in the actual table because I want those return values of "final_original_price" and "final_sale_price" to do the calculations.

我现在想在 SELECT 中添加一行以获得折扣。我无法使用实际表中的现有字段，因为我希望“final_original_price”和“final_sale_price”的返回值进行计算。

e.g.

例如

SELECT  p.productid, p.stylename,
CASE    WHEN (ppo.original_price IS NOT NULL) THEN ppo.original_price ELSE pp.original_price END AS final_original_price,
CASE    WHEN (ppo.original_price IS NOT NULL) THEN ppo.sale_price ELSE pp.sale_price END AS final_sale_price,

((final_original_price - final_sale_price) / final_original_price * 100) AS final_discount_percentage 

FROM product p, ... etc.

The above does not work, as Postgresql returns "ERROR: column "final_original_price" does not exist at character....."

以上不起作用，因为 Postgresql 返回“错误：列“final_original_price”在字符处不存在......”

So, clearly I can't use the return value of the CASE. My questions on what solution to use:

所以，显然我不能使用 CASE 的返回值。我关于使用什么解决方案的问题：

1) Can I indeed use the return value of the case like I want to, above? OR 2) Do I need to again plug in the case statement to my calculation? That means I need to repeat the CASE code and the query will look quite lengthy. If I have to, I'd do this, but I'm just wondering if there's a better way. OR 3) I could also store an additional field in the database to store the discount. This data would be redundant because my CMS would need to ensure that the field is updated whenever the price is updated. However it would save heavy calculations (if the above is considered heavy) on the front end which runs much more often than the backend CMS.

1）我确实可以像上面那样使用案例的返回值吗？或 2) 我是否需要再次将 case 语句插入到我的计算中？这意味着我需要重复 CASE 代码并且查询看起来很长。如果必须的话，我会这样做，但我只是想知道是否有更好的方法。或者 3) 我还可以在数据库中存储一个额外的字段来存储折扣。此数据将是多余的，因为我的 CMS 需要确保在价格更新时更新该字段。然而，它会在前端节省大量计算（如果以上被认为是繁重的），它比后端 CMS 运行得更频繁。

The above solutions are probably the easiest, but I am also wondering, if I had time to do this, are there any other solutions here that would be worth considering? For example would this be a good scenarios to write a "view"? Personally I have never set up a view and as far as I understand, the database-selection work is still happening in the background, but, if set up, would make the end query above simpler to understand from a developer's point of view.

上述解决方案可能是最简单的，但我也想知道，如果我有时间这样做，这里还有其他值得考虑的解决方案吗？例如，这是编写“视图”的好场景吗？就我个人而言，我从未设置过视图，据我所知，数据库选择工作仍在后台进行，但是，如果设置了，将从开发人员的角度更容易理解上面的最终查询。

Many thanks!

非常感谢！

回答by Yahia

use

用

SELECT
productid, 
stylename,
final_original_price,
final_sale_price,
((final_original_price - final_sale_price) / final_original_price * 100) AS final_discount_percentage 
FROM
(
SELECT  p.productid, p.stylename,
CASE    WHEN (ppo.original_price IS NOT NULL) THEN ppo.original_price ELSE pp.original_price END AS final_original_price,
CASE    WHEN (ppo.original_price IS NOT NULL) THEN ppo.sale_price ELSE pp.sale_price END AS final_sale_price
FROM product p, ... etc.
)

The above does exactly what you asked for... if for some reason you don't want to use it then plugin the CASEstatements into the calculation (option 2 from your question).

以上完全符合您的要求...如果出于某种原因您不想使用它，则将CASE语句插入计算中（您问题中的选项 2）。