How to implement score points for each WHERE clause in SELECT statement-CodePudding

how can i create column with information about how much condition was passed for each field? eg. I have client who find property with max price to 500000, 3th floor and living area between 45 meters. Now when i use "AND" for each condidtions i will get rows with 100% compatibility. But What abaout to find rows with the same condidtions as before but without living area in range. There will be 66% copatibility because 2/3 of my conditions is passed.

There is my sqlfiddle http://sqlfiddle.com/#!9/1ef60c/5

CodePudding user response：

Simple way to solve your problem is:

SELECT *,
        (
         CASE WHEN `property_max-price` < 550000 THEN 1 ELSE 0 END 
            
         CASE WHEN property_additional LIKE "%hot_water%" THEN 1 ELSE 0 END
           
         CASE WHEN  `property_floor-from` >= 2 AND `property_floor-to` <=5 THEN 1 ELSE 0 END
        ) / 3 * 100 AS `%`
FROM client