mysql查询中的条件除外. [英] except condition in mysql query..

问题描述

我有一个具有以下结构的表

i have table with following structure,

name (type: TEXT)
price (type: INT)
color (type: TEXT)
vehicletype (type: TEXT) eg. SEDAN/SUV 

现在我要检索(名称，价格，颜色，车辆类型)如果它是黑色轿车，我准备支付10,000，但是如果它是红色或白色，则不超过8,000.对于其他任何一种颜色，除非是SUV，否则我的价格都不会超过7,000，在这种情况下，黑色的预算最多为15,000，其他任何颜色的预算都为14,000.
我有这个查询，但是没用，

now i want to retrieve (name, price, color, vehicletype) If its a black sedan, I'm ready to pay 10,000, but if its red or white, then no more than 8,000. For any other color I won't go above 7,000, except if its an SUV, in which case my budget is upto 15,000 for a black one or upto 14,000 for any other color.
i have this query, but it's not worked,

SELECT name, price, color, vehicletype FROM carrecords WHERE
(vehicletype = 'SEDAN' AND color  = 'black' AND price <= 10000) OR
(vehicletype = 'SEDAN' AND color IN('red','white') AND price <= 8000 ) OR
(vehicletype  = 'SEDAN' /* here I tried != "SUV" as well - no luck*/  AND color NOT IN('red','white','black') AND price <= 7000) OR
(vehicletype = 'SUV' AND color  = 'black' AND price <= 15000) OR
(vehicletype = 'SUV' AND color  != 'black' AND price <= 14000)
ORDER BY price ASC  

那么，有什么解决办法吗?

so, is there any solution?

推荐答案

SELECT name, price, color, vehicletype FROM carrecords WHERE
-- black sedans up to 10k
(vehicletype = 'SEDAN' AND color  = 'black' AND price <= 10000)
-- red or white sedans up to 8k
OR (vehicletype = 'SEDAN' AND color IN('red','white') AND price <= 8000 )
-- black SUV up to 15k
OR (vehicletype = 'SUV' AND color  = 'black' AND price <= 15000)
-- non-black SUV up to 14k
OR (vehicletype = 'SUV' AND color  != 'black' AND price <= 14000)
-- any other vehicle up to 7k
OR (price <= 7000)
ORDER BY price ASC  

这篇关于mysql查询中的条件除外.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！