类似mysql查询顺序的搜索词

Question

我有两张桌子

create table crm_location_site (loc_name varchar(100),loc_id int(11) );
insert into crm_location_site values 
('Dubai Marina',11),
('Marina Residance',12),
('Jumera Marina',13)
create table crm_subloc (sub_sub_loc varchar(100),sub_loc_id int(11) );
insert into crm_subloc values 
('Ora Marina',11),
('Grand Ora Marina',12)

这是我的查询

SELECT loc_id as id,loc_name as name,'loc' as tbl 
FROM crm_location_site
WHERE TRIM(loc_name) LIKE '%M%' 
UNION
SELECT sub_loc_id as id,sub_sub_loc as name,'subloc' as tbl 
FROM crm_subloc
WHERE TRIM(sub_sub_loc) LIKE '%M%'

这个查询给了我

Dubai Marina
Marina Residance
Jumera marina

我想表现得与从顶端开始相匹配。

就像这样

Marina Residance
Dubai Marina
Jumera marina

这里是一个简单的小提琴小提琴

Answer 1

像这样吗？

SELECT *
FROM
(
    SELECT loc_id as id,loc_name as name,'loc' as tbl 
    FROM crm_location_site
    WHERE TRIM(loc_name) LIKE '%M%' 
    UNION
    SELECT sub_loc_id as id,sub_sub_loc as name,'subloc' as tbl 
    FROM crm_subloc
    WHERE TRIM(sub_sub_loc) LIKE '%M%'
) x
ORDER BY POSITION('M' in name);

仅供参考

按名称排序，重复次数最多的

ORDER BY (LENGTH(name) - LENGTH(REPLACE('M', '', name))) / LENGTH('M') DESC;

Answer 2

类似这样的东西

|ID |   NAME             |TBL|
|12 |   Marina Residance |loc|
|11 |   Dubai Marina     |loc|
|13 |   Jumera Marina    |  loc|
|11 |   Ora Marina       |subloc|
|12 |   Grand Ora Marina |subloc|

SELECT loc_id AS id,loc_name AS name,'loc' AS tbl
FROM crm_location_site
WHERE TRIM(loc_name) LIKE 'M%'
UNION 
SELECT sub_loc_id AS id,sub_sub_loc AS name,'subloc' AS tbl
FROM crm_subloc
WHERE TRIM(sub_sub_loc) LIKE 'M%'
UNION  
SELECT loc_id AS id,loc_name AS name,'loc' AS tbl
FROM crm_location_site
WHERE TRIM(loc_name) LIKE '%M%'
UNION 
SELECT sub_loc_id AS id,sub_sub_loc AS name,'subloc' AS tbl
FROM crm_subloc
WHERE TRIM(sub_sub_loc) LIKE '%M%'

这里的琴

Answer 3

http://sqlfiddle.com/#!2/ad8331/11

SELECT loc_id as id,loc_name as name,'loc' as tbl 
FROM crm_location_site
WHERE TRIM(loc_name) LIKE '%M%' 
UNION
SELECT sub_loc_id as id,sub_sub_loc as name,'subloc' as tbl 
FROM crm_subloc
WHERE TRIM(sub_sub_loc) LIKE '%M%'
ORDER BY POSITION('M' in name)

或http://sqlfiddle.com/#!2/ad8331/14

SELECT loc_id as id,loc_name as name,'loc' as tbl 
FROM crm_location_site
WHERE TRIM(loc_name) LIKE '%M%' 
UNION
SELECT sub_loc_id as id,sub_sub_loc as name,'subloc' as tbl 
FROM crm_subloc
WHERE TRIM(sub_sub_loc) LIKE '%M%'
ORDER BY IF (name LIKE 'M%', 0, 1)