问题描述:

I have a query like :

SELECT * FROM mytable WHERE (`field1` LIKE '%search%' OR `field2` LIKE '%search%' OR `field3` LIKE '%search%')

Keyword search can be any other words. How can i set priority to this query ? I want to search first in field1, after in field2 and then in field3.

So if I find my keyword search in field1, row with search in field1 must be in first in my results set.

How can i do that ?

网友答案:

You can do it with order by:

SELECT * 
FROM mytable 
WHERE  (`field1` LIKE '%search%' OR `field2` LIKE '%search%' OR `field3` LIKE '%search%')
ORDER BY 
CASE 
  WHEN `field1` LIKE '%search%' THEN 1
  WHEN `field2` LIKE '%search%' THEN 2
  WHEN `field3` LIKE '%search%' THEN 3
  ELSE 4
END 
网友答案:

You'd better split query and union them.

select *
    from (
    SELECT 1 as s, * FROM mytable WHERE  ('field1' LIKE '%search%')
    union
    SELECT 2 as s, * FROM mytable WHERE  ('field2' LIKE '%search%')
    union
    SELECT 3 as s, * FROM mytable WHERE  ('field3' LIKE '%search%')
) a 
order by s
网友答案:

Order by the boolean result:

SELECT * FROM mytable WHERE
    (
      `field1` LIKE '%search%' OR `field2` LIKE '%search%' 
      OR `field3` LIKE '%search%'
    )
order by `field1` LIKE '%search%' DESC

You need DESC because by default false is usually ordered before true

网友答案:
if exists (select top 1 * form mytable where 'field1' like '%search%')
 select * form mytable where 'field1' like'%search%'
else if exists (select top 1* form mytable where 'field2' like '%search%')
 select * form mytable where 'field2' like'%search%'
else
 select * form mytable where 'field3' like'%search%'
相关阅读:
Top