sql - 在查询中按参数匹配计数搜索排序？数据库

Question

我正在 PostgreSQL 中处理搜索查询，我所做的其中一件事是根据匹配的参数数量对查询结果进行排序。我不知道如何做到这一点。有人有建议或解决方案吗？

 Table
 brand     color     type     engine
 Ford      Blue      4-door   V8
 Maserati  Blue      2-door   V12
 Saturn    Green     4-door   V8
 GM        Yellow    1-door   V4

 Current Query
 SELECT brand FROM table WHERE color = 'Blue' or type = '4-door' or engine = 'V8'

 Result Should Be
 Ford      (3 match)
 Saturn    (2 match)
 Maserati  (1 match)

Answer 1

我认为你应该能够做到:

ORDER BY
    CASE WHEN color = 'Blue' THEN 1 ELSE 0 END +
    CASE WHEN type = '4-door' THEN 1 ELSE 0 END +
    CASE WHEN engine = 'V8' THEN 1 ELSE 0 END DESC