mysql - SQL计算SELECT时的折扣

Question

我想为表选择我的数据库，并希望在选择时使用之前的价格和价格(即现在的价格)计算折扣。

这是我的查询

$sql = "SELECT *, SUM((pricebefore - price ) / price * 100) AS discount FROM products  WHERE price IS NOT NULL ORDER BY ID desc LIMIT $no_of_records_per_page OFFSET $offset";

所以我想获取所有数据，并对之前的价格求和 - 价格比/价格和 * 100，以便我获得百分比折扣并希望将其安全为折扣。

但是我一直收到这个错误:

Query failed: ERROR:  operator does not exist: character varying - character varying
LINE 1: SELECT *, SUM((pricebefore - price ) / price * 100) AS disco...
                                   ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

但是为什么......我没有发现问题，尝试了很多东西，比如没有 SUM 或其他东西，但没有任何效果

Answer 1

如果 price_before 位于同一行，则不需要 sum()。

SELECT p.*, (p.pricebefore - p.price ) / p.price * 100 AS discount
FROM products
WHERE price IS NOT NULL
ORDER BY ID DESC
LIMIT $no_of_records_per_page OFFSET $offset;

看起来 pricebefore 和 price 是字符串而不是数字。 MySQL 通常会转换它们，但您可以进行显式转换:

SELECT p.*, ( cast(p.pricebefore as numeric(20, 4)) - cast(p.price as numeric(20, 4)) / cast(p.price as numeric(20, 40)) * 100 AS discount
FROM products
WHERE price IS NOT NULL
ORDER BY ID DESC
LIMIT $no_of_records_per_page OFFSET $offset;