mysql - mysql WHERE CLAUSE 中的百分比

Question

我想让所有记录的价格都比给定值低 20% 或高。

+----+------------+----------+---------+
| ID | Item        | Category |  price  |       
+----+------------+----------+-----------+
|  1 | Popular     | Rock     |  1000
|  2 | Classical   | Opera    |  5000 
|  3 | Popular2    | Jazz     |  6000
|  4 | Classical2  | Dance    |  8000
|  5 | Classical3  | General  |  4825
+----+------------+----------+------------+

用户必须传递两个参数

i - 类别

ii-价格

我需要显示所有具有相同类别且价格比用户值(value)低 20% 到高 20% 的商品。像这样

SELECT * FROM Table1 WHERE Categry='ROCK' AND price > (20% less price of user's price) AND price < (20% greater than user's price)

Answer 1

假设@price代表用户的价格:

SELECT * 
FROM   Table1 
WHERE  Categry='ROCK' AND price BETWEEN (0.8 * @price) AND price < (1.2 * @price)