组合 Mysql 子查询

Question

我有一个产品表。 要执行搜索，请使用 3 个不同的查询来获得最匹配的结果。 但是我得到的很少无赖，例如分页，因此需要将我的 3 个查询合并为一个，并且仍然给出相同的结果。

以下是我的查询和对每个查询的解释

查询1：此查询获取与搜索字符串完全匹配的内容

SELECT *, 
MATCH (product_name) AGAINST ('k' IN BOOLEAN MODE) as distance, 
MATCH (product_description) AGAINST ('k' IN BOOLEAN MODE) as distance2 
FROM  products 
INNER JOIN shops ON shops.shop_id = products.shop_id 
WHERE MATCH  (product_name) AGAINST ('k') 
OR MATCH (product_description) AGAINST ('k') 
ORDER BY distance DESC, distance2 DESC 
LIMIT 0, 9;

查询2：此查询获取搜索字符串是字符串一部分的所有结果减去查询结果，然后将结果添加到数组中

SELECT * 
FROM products 
INNER JOIN shops ON shops.shop_id = products.shop_id 
WHERE product_name LIKE '%k%' 
ORDER BY INSTR(product_name,'k'), product_name 
LIMIT 0, 9;

查询3：此查询与搜索字符串最接近匹配，减去查询 1 的结果，查询 2 将结果添加到数组

SELECT *, 
fuzzy_substring( 'k', product_name ) as distance, 
fuzzy_substring( 'k', product_description ) as distance2 
FROM products 
INNER JOIN shops ON shops.shop_id = products.shop_id 
WHERE (fuzzy_substring( 'k', product_name ) < 3 
OR fuzzy_substring( 'k', product_description ) < 3) 
AND product_id NOT IN ('pr933j4', 'Posr49il', 'p4021dd') 
order by distance ASC, distance2 ASC 
LIMIT 0, 9;

我不知道如何将这三个查询结合起来，任何正确的建议都非常感谢。注意 'k' 是正在搜索的字符串

Answer 1

UNION 为我工作

SELECT * FROM 
( (SELECT j1.* FROM products AS j1 LEFT JOIN shops AS z1 ON z1.shop_id = 
j1.shop_id WHERE MATCH (product_name) AGAINST ('k') OR MATCH 
(product_description) AGAINST ('k') ORDER BY MATCH (product_name) 
AGAINST ('k' IN BOOLEAN MODE) DESC, MATCH (product_description) AGAINST 
('k' IN BOOLEAN MODE) DESC ) s1)
UNION 
SELECT * FROM 
( ( SELECT j2.* FROM products AS j2 LEFT JOIN shops AS z2 ON 
z2.shop_id = j2.shop_id WHERE product_name LIKE '%k%' ORDER BY 
INSTR(product_name,'k'), product_name ) s2)
UNION 
SELECT * FROM 
( ( SELECT j3.* FROM products as j3 LEFT JOIN shops as z3 ON z3.shop_id = 
j3.shop_id WHERE (fuzzy_substring( 'k', product_name ) < 3 OR 
fuzzy_substring( 'k', product_description ) < 3) AND product_id NOT IN 
('pr933j4', 'Posr49il', 'p4021dd') order by fuzzy_substring( 'k', 
product_name ) ASC, fuzzy_substring( 'k', product_description ) ASC ) 
s3)