Sort SQL Server records by the number of OR-compatible conditions

Suppose I have a (code-generated) query that is looking for keywords in a number of different fields. For example, any one of the terms University
, South
or Africa
might be in the name field, or the address field, or some other field. I want to find all records where any of these fields match any of these keywords:

SELECT *
FROM
    Institutions
WHERE
    Institutions.name LIKE '%University%' OR
    Institutions.address LIKE '%University%' OR
    Institutions.name LIKE '%South%' OR
    Institutions.address LIKE '%South%' OR
    Institutions.name LIKE '%Africa%' OR
    Institutions.address LIKE '%Africa%'

However, this is going to pull up everything
. I would like to sort by the records that match the most possible conditions.

I think this would work but looks pretty clunky:

SELECT *
FROM
    Institutions
WHERE
    Institutions.name LIKE '%University%' OR
    Institutions.address LIKE '%University%' OR
    Institutions.name LIKE '%South%' OR
    Institutions.address LIKE '%South%' OR
    Institutions.name LIKE '%Africa%' OR
    Institutions.address LIKE '%Africa%'
ORDER BY
    (
        CASE WHEN Institutions.name LIKE '%University%' THEN 1 ELSE 0 END +
        CASE WHEN Institutions.address LIKE '%University%' THEN 1 ELSE 0 END +
        CASE WHEN Institutions.name LIKE '%South%' THEN 1 ELSE 0 END +
        CASE WHEN Institutions.address LIKE '%South%' THEN 1 ELSE 0 END +
        CASE WHEN Institutions.name LIKE '%Africa%' THEN 1 ELSE 0 END +
        CASE WHEN Institutions.address LIKE '%Africa%' THEN 1 ELSE 0 END
    ) DESC

Is there something cleaner? I’m fine with SQL Server-specific solutions but pure SQL solutions would be great.

Your solution looks okay to me, but here’s something else that should work (pseudo-code):

SELECT * FROM
(SELECT *,COUNT(*) AS cnt FROM
(SELECT * FROM I
WHERE name LIKE '%etc%'
UNION ALL
SELECT * FROM I
WHERE name LIKE '%etc2%'
-- etc. )
GROUP BY *)
ORDER BY cnt DESC;
Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Sort SQL Server records by the number of OR-compatible conditions

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录