I am building a user search and I have a Users table with id, first, and last in it.
I then query a names, say: "John Smith".
Using PHP I'll break the query into 2 parts: q1 = John and q2 = Smith.
I want to devise a points system where the record that has first = John and last = Smith gets pulled to the top.
So my sql is something like this:
SELECT User.id, User.first, User.last
FROM users as User
WHERE (User.first = q1 OR User.first = q2) OR //if this is true +1 points
WHERE (User.last = q1 OR User.last 开发者_JAVA技巧= q2) OR //if this is true +1 points
//then sum up the points and order by it (so the User.id with John Smith will have 2 points)
Any suggestions?
If your DBMS has an IF function you can do:
SELECT User.id, User.first, User.last,
IF (User.first = q1 OR User.first = q2, 1, 0) +
IF (User.last = q1 OR User.last = q2, 1, 0) as points
FROM users as User
ORDER BY points DESC
精彩评论