好吧,我已经用这个一段时间了,我有点卡住了。
也许我做错了!
基本上我有一个搜索字段的查询。 一般的想法是根据 LIKE %% 选择结果,同时仍然将完全匹配放在首位。
因此,例如,如果您搜索 47,我希望显示 id、姓氏或 company_name 中带有 47 的所有内容,但是 ID 号 47 的结果应该在顶部,如果我输入姓氏,则结果应该相同。
请参阅下面的代码,它可能有助于澄清我的问题。
SELECT id,
IF(company_name IS NOT NULL AND company_name <> '', company_name, surname) AS name,
first_name, country, phone1, isowner, isholidayrenter, isproholidayrenter,
islongtermrenter, isprolongtermrenter, isprobuyer, isbuyer
FROM clients
WHERE id LIKE '$search' OR surname LIKE '$search' OR company_name LIKE '$search'
union all
SELECT id,
IF(company_name IS NOT NULL AND company_name <> '', company_name, surname) AS name,
first_name, country, phone1, isowner, isholidayrenter, isproholidayrenter,
islongtermrenter, isprolongtermrenter, isprobuyer, isbuyer
FROM clients
WHERE id LIKE '%$search%' AND id NOT LIKE '$search' OR surname LIKE '%$search%'
and SURNAME NOT LIKE '$search' OR company_name LIKE '%$search%'
and company_name NOT LIKE '$search'
LIMIT $start, $limit";
'
试试这个:
(选择完全匹配)全部与(选择部分匹配省略完全匹配)
示例:
(
SELECT
id,
IF( company_name IS NOT NULL AND company_name <> '', company_name, surname ) AS name,
first_name, country, phone1, isowner, isholidayrenter, isproholidayrenter,
islongtermrenter, isprolongtermrenter, isprobuyer, isbuyer
FROM
clients
WHERE
id LIKE '$search' OR
surname LIKE '$search' OR
company_name LIKE '$search'
)
union all
(
SELECT
id,
IF( company_name IS NOT NULL AND company_name <> '', company_name, surname ) AS name,
first_name, country, phone1, isowner, isholidayrenter, isproholidayrenter,
islongtermrenter, isprolongtermrenter, isprobuyer, isbuyer
FROM
clients
WHERE
( id LIKE '%$search%' AND id NOT LIKE '$search' ) OR
( surname LIKE '%$search%' AND SURNAME NOT LIKE '$search' ) OR
( company_name LIKE '%$search%' AND company_name NOT LIKE '$search' )
)
LIMIT $start, $limit;