我有一个包含currency_code
和price
的mysql表。
currency_code | price
usd | 50
twd | 1570
JPY | 5049
用户可以输入price
的范围来搜索产品:
SELECT * FROM product WHERE price between 0 and 1500
如果表中的currency_code
不同,如何进行price
范围查询?
您必须控制所选货币并将其放入查询中:
SELECT * FROM product WHERE price between 0 and 1500
AND currency_code = $selected_currency_code
根据您的问题,我了解到我们有两个表,如下所示为了便于理解,请检查这些样品
价格
id |currency_id |price
----------------------------
1 |1 |1000
2 |2 |1500
3 |1 |1300
货币
currency_id | currency_name
--------------------------------
1 | Dollor
2 | Euro
这是一个示例联接查询,可以输出0到1500 之间的值
SELECT price.id,price.price,currency_code.currency_name FROM price
INNER JOIN currency_code ON price.currency_id = currency_code.currency_id
WHERE price BETWEEN 0 AND 1500
select driver.*,
case
when driver.rental_currency = 'HKD' then driver_rental.HKT_1_fee_full_day / 7.78
when driver.rental_currency = 'TWD' then driver_rental.HKT_1_fee_full_day / 31.4
when driver.rental_currency = 'CNY' then driver_rental.HKT_1_fee_full_day / 6.67
end
as HKT_1_fee_full_day
from driver_rental
left join driver
on
driver.email = driver_rental.email
where
driver_rental.HKT_1_fee_full_day between 0 and 52
;
用例后where语句之间不能工作?