查询联合，如果搜索结果小于10，则从数据库按日期顺序填充其他结果 - Query union, if search result is less than 10, fill with others from database order by date

Query union, if search result is less than 10, fill with others from database order by date

我想做一个mysql联合搜索。我的目的是:我的总成绩必须是10分。

如果搜索结果大于10，则返回的数据全部来自搜索结果。
如果搜索结果小于10个，则先从搜索结果中返回少量数据，然后按日期顺序从数据库中获取剩余的结果。

为了更清楚:如果客户端搜索"今天"，我的数据库只返回7个包含"今天"的结果，然后从我的数据库中添加另外3个结果ORDER BY date。所以总共结果是10项。

另一个目的:另外3个结果与匹配搜索的7个结果不重复。我认为UNION或UNION DISTINCT可以完成这项工作，对吗?

我怎么做这样的查询呢?

PS:我的代码将修复结果顺序，但我需要首先select总是落后于第二个select

(SELECT * FROM table WHERE title like %$searchword% limit 0,10 ORDER BY date)
UNION
(SELECT * FROM table limit 0,10 ORDER BY date)
limit 0,10 ORDER BY date

如果你总是想要10个结果:

SELECT 
    IF(m.id,1,0) AS has_match,
    t.*
FROM 
    `table` t
    LEFT JOIN `table` m ON m.id = t.id AND m.title LIKE '%$searchword%'
GROUP BY t.id
ORDER BY has_match DESC, date
LIMIT 10

测试:

mysql> select * from `table`;
+----+------------------------+---------------------+
| id | title                  | date                |
+----+------------------------+---------------------+
|  1 | test 1                 | 2011-11-06 10:27:08 |
|  2 | test 2 match           | 2011-11-06 10:27:14 |
|  3 | 3 match this too       | 2011-11-06 10:27:23 |
|  4 | title does NOT         | 2011-11-06 10:27:44 |
|  5 | Another matching title | 2011-11-06 10:27:55 |
|  6 | this does not either   | 2011-11-06 10:29:22 |
|  7 | Do not put this first  | 2011-11-06 10:29:37 |
|  8 | Is this number 8?      | 2011-11-06 10:29:57 |
|  9 | The 9th is a match     | 2011-11-06 10:30:07 |
| 10 | 10th does not          | 2011-11-06 10:30:20 |
| 11 | 11th IS a match too!   | 2011-11-06 10:30:37 |
| 12 | 12th gets ignored?     | 2011-11-06 10:30:49 |
+----+------------------------+---------------------+
12 rows in set (0.00 sec)
mysql> SELECT IF(m.id,1,0) AS has_match, t.* FROM `table` t LEFT JOIN `table` m ON m.id = t.id AND m.title LIKE '%match%' GROUP BY t.id ORDER BY has_match DESC, date LIMIT 10;
+-----------+----+------------------------+---------------------+
| has_match | id | title                  | date                |
+-----------+----+------------------------+---------------------+
|         1 |  2 | test 2 match           | 2011-11-06 10:27:14 |
|         1 |  3 | 3 match this too       | 2011-11-06 10:27:23 |
|         1 |  5 | Another matching title | 2011-11-06 10:27:55 |
|         1 |  9 | The 9th is a match     | 2011-11-06 10:30:07 |
|         1 | 11 | 11th IS a match too!   | 2011-11-06 10:30:37 |
|         0 |  1 | test 1                 | 2011-11-06 10:27:08 |
|         0 |  4 | title does NOT         | 2011-11-06 10:27:44 |
|         0 |  6 | this does not either   | 2011-11-06 10:29:22 |
|         0 |  7 | Do not put this first  | 2011-11-06 10:29:37 |
|         0 |  8 | Is this number 8?      | 2011-11-06 10:29:57 |
+-----------+----+------------------------+---------------------+
10 rows in set (0.00 sec)