问题描述
我有一个包含多个表的产品数据库来存储定价（由于每种产品颜色的默认定价和可选的替代定价），并且我的查询中包含CASE语句，以获取
*产品的原始价格
*产品的销售价格（如果正在销售）
如果产品正在销售，我还需要计算产品的折扣。在尝试此操作之前，请先查看现有SQL的细目分类。
SELECT p.productid，p.stylename，
情况为（ppo.original_price不为空）则为ppo.original_price ELSE pp.original_price END为final_original_price，
情况为（ppo.original_price不为空）则为ppo.sale_price ELSE pp.sale_price END AS
FROM product p，...等。
上面的代码有效（我已经简化了），基本上，产品的原始价格存储在列别名 final_original_price中，而销售价格（可能为NULL）则返回为 final_sale_price。
我现在想在SELECT上添加一行以获取折扣。我不能使用实际表中的现有字段，因为我希望这些返回值 final_original_price和 final_sale_price进行计算。
eg
选择p.productid，p.stylename，
时（ppo.original_price不为空）的情况然后ppo.original_price ELSE pp .original_price END AS final_original_price，
时（ppo.original_price不为空），然后ppo.sale_price ELSE pp.sale_price END AS final_sale_price，
（（final_original_price-final_sale_price）/ final_original ）AS final_discount_percentage
FROM product p，... etc.
上面的方法不起作用，因为Postgresql返回错误：字符处的列 final_original_price不存在.....
因此，显然我不能使用CASE的返回值。我对使用哪种解决方案有疑问：
1）确实可以在上面使用我想使用的情况的返回值吗？
或
2）我是否需要再次将case语句插入计算中？这意味着我需要重复CASE代码，查询看起来会很冗长。如果需要，我会这样做，但我只是想知道是否有更好的方法。
或
3）我也可以在数据库中存储一个附加字段以存储折扣。该数据将是多余的，因为每当价格更新时，我的CMS都需要确保该字段已更新。但是，这样可以节省前端（相比上面的CMS而言）运行频率更高的前端的繁重计算（如果考虑到以上内容）。
上述解决方案可能是最简单的，但我也想知道，如果我有时间这样做，这里是否还有其他值得考虑的解决方案？例如，写视图会是一个好场景吗？就我个人而言，我从未建立过视图，据我所知，数据库选择工作仍在后台进行，但是，如果进行了设置，从开发人员的角度来看，将使上面的结束查询更容易理解。 / p>
非常感谢！
使用
选择
productid，
样式名，
final_original_price，
final_sale_price，
（（final_original_price- final_sale_price）/ final_original_price * 100）AS final_discount_percentage
从
（
选择p.productid，p.stylename，
的情况（ppo.original_price不为空）THEN ppo.original_price ELSE pp.original_price END as final_original_price，
时（ppo.original_price不为空）的情况THEN ppo.sale_price ELSE pp.sale_price END AS final_sale_price
来自产品p，...等。
）
以上内容确实符合您的要求...如果出于某些原因您不想要然后用它将 CASE 语句插入计算（问题中的选项2）。
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
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.
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".
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.
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....."
So, clearly I can't use the return value of the CASE. My questions on what solution to use:
1) Can I indeed use the return value of the case like I want to, above?OR2) 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.OR3) 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.
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!
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 CASE statements into the calculation (option 2 from your question).
这篇关于PostgreSQL CASE语句-我可以在SELECT中使用CASE的返回值吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！