mysql - 如何将同一个表的两个查询放在一起

Question

我无法将两个查询合并为一个。

查询1:

SELECT *
FROM (`text`)
WHERE `text` LIKE '%TERM1%' ORDER BY `text`.`start` DESC

查询2:

SELECT *,
  MATCH (col1,  col2,  col3) AGAINST ('%TERM2%' IN BOOLEAN MODE) AS relevance
FROM `text`
WHERE MATCH (col1, col2, col3) AGAINST ('%TERM2%' IN BOOLEAN MODE)
      AND status=2
ORDER BY `text`.`start` DESC

任何建议都会被接受。

表格“文本”看起来像

+-------+--------+-------+------+------+
|  col1 |  col2  | col3  | text |  id  |
+-------+--------+-------+------+------+
| name1 | place1 | date1 | val1 |   1  |
| name2 | place2 | date2 | val2 |   2  |
| name3 | place2 | date5 | val1 |   3  |
| name4 | place4 | date4 | val5 |   4  |
| name5 | place5 | date5 | val5 |   5  |
:
:
| namex | placex | datex | valx |   x  |
+-------+--------+-------+------+------+

我忘记写了，第一个查询也必须按start DESC排序...

我期望的结果如下:term1 = val1, and term2 = val5

+-------+--------+-------+------+------+
|  col1 |  col2  | col3  | text |  id  |
+-------+--------+-------+------+------+

qery1 结果:

| name3 | place2 | date5 | val1 |   3  |
| name1 | place1 | date1 | val1 |   1  |

查询2结果

| name5 | place5 | date5 | val5 |   5  |
| name4 | place4 | date4 | val5 |   4  |
:
:
| namex | placex | datex | valx |   1  |
+-------+--------+-------+------+------+

谢谢。

@PinnyM - 是的，表格名为text，表格内立柱text，不幸的是，我采用了具有完整数据库的项目，这很荒谬，但也是如此因为有必要或不需要更改表名而去更改完整的代码是荒谬的。

Answer 1

这是一种方法:

SELECT t.*, -1 as relevance
FROM `text` t
WHERE `text` LIKE '%TERM1%'
union all
SELECT t.*, MATCH (col1,  col2,  col3) AGAINST ('%TERM2%' IN BOOLEAN MODE) AS relevance
FROM `text` t
WHERE MATCH (col1, col2, col3) AGAINST ('%TERM2%' IN BOOLEAN MODE) AND status=2
ORDER BY `text`.`start` DESC