Mysql 对匹配两个或多个表的表结果进行排序

发布于07月08日

我有一个SELECT语句，它从两个表中获取数据，然后执行类似这样的MATCH语句:

SELECT a.*, b.* 
 FROM a INNER JOIN b ON a.id=b.id 
 WHERE MATCH(a.column) AGAINST( 'somthing' IN BOLLEAN MODE) 
   OR MATCH(b.column) AGAINST( 'somthing' IN BOLLEAN MODE) 
 ORDER BY

我想把结果排序为a AND b BOTH match on the same row，然后是a OR b match in a row (order doesn't mater for this)

任何帮助都是徒劳的！

推荐答案

由于全文搜索不在SELECT中，因此需要重复它们.

剩下的就是决定哪个先来的情况了

SELECT a.*, b.* 
 FROM a INNER JOIN b ON a.id=b.id 
 WHERE MATCH(a.column) AGAINST( 'somthing' IN BOoLEAN MODE) 
   OR MATCH(b.column) AGAINST( 'somthing' IN BOOLEAN MODE) 
 ORDER BY 
CASe WHEN MATCH(a.column) AGAINST( 'somthing' IN BOoLEAN MODE) > 0 AND MATCH(b.column) AGAINST( 'somthing' IN BOOLEAN MODE)  > 0 THEN 1
WHEN MATCH(a.column) AGAINST( 'somthing' IN BOoLEAN MODE) > 0 OR MATCH(b.column) AGAINST( 'somthing' IN BOOLEAN MODE)  > 0 THEN 2 END