开发者

How would I order a table by the number of matching params in the where clause of an sql statement?

I'm writing sql to search a database by a number of parameters. How would I go about ordering the result set by the items that match the most parameters in the where clause. For example:

SELECT * 
FROM users 
WHERE username = 'eitan' 
OR email = 'eitan@eitan.com' 
OR company = 'eitan'


Username   |  email                |   company

1) eitan   |     blah@blah.com     |     blah

2) eitan   |     eitan@eitan.com   |    eitan

3) eitan   |    eitan@eitan.com    |     blah

should be ordere开发者_开发知识库d like:

2, 3, 1.

Thanks. (ps the query isn't that easy, has a lot of joins and a lot of OR's in the WHERE)

Eitan


If MySQL:

SELECT * FROM users 
ORDER BY 
    (username = 'eitan') 
    + (email = 'eitan@eitan.com') 
    + (company = 'eitan') 
    DESC

If PostgreSQL:

SELECT * FROM users 
ORDER BY 
    (username = 'eitan')::int 
    + (email = 'eitan@eitan.com')::int 
    + (company = 'eitan')::int 
    DESC

If Sql Server:

SELECT * FROM users 
ORDER BY 
    case when username = 'eitan' then 1 else 0 end
    + case when email = 'eitan@eitan.com' then 1 else 0 end
    + case when company = 'eitan' then 1 else 0 end
    DESC
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜