Mysql 联合 2 查询包含顺序依据和随机

Question

我有一个包含以下列的表格： id (AI)，状态(布尔值 0 或 1)，创建时间(日期时间)

我想获取状态 = 1 的记录并按在 DESC 创建的记录进行排序，另一个获取状态 = 0 的随机记录

我按如下方式运行代码，但无法返回正确答案。

(SELECT *
FROM
`Teacher` AS `teacher`
WHERE
`teacher`.`status` = 1 ORDER BY `created_at` desc)
UNION 
(SELECT *
FROM
`Teacher` AS `teacher`
WHERE 
`teacher`.`status` = 0 ORDER By rand())

Answer 1

UNIONset 运算符强制执行"使用文件排序"操作来标识和删除重复项。

UNION ALL集运算符将连接两个结果集，而无需调用排序操作来标识和删除重复项。

但是，如果只是用UNION ALL替换UNION，我们将依赖于无法保证的行为。 如果我们需要按特定顺序返回的结果，那么在最外层的查询上应该有一个ORDER BY子句。

https://dev.mysql.com/doc/refman/8.0/en/union.html

摘录

但是，对单个SELECT语句使用ORDER BY并不意味着行在最终结果中的显示顺序，因为默认情况下UNION生成一组无序行。

。

(如果有人向其中一个表添加列，这将导致此查询失败，返回的列数不匹配。

(在第二个 SELECT 中存在对teacher.status的无效引用。

(就个人而言，我会为每个集合添加一个鉴别器列，然后在外部查询的ORDER BY子句中引用该列。我还会放弃 SELECT 列表中的*并指定要返回的列。

---

我将通过这样的东西实现指定的结果：

( 
SELECT 'teacher' AS src
, t.fee
, t.fi
, t.fo
, t.fum
FROM `Teacher` t
WHERE t.`status` = 1
)
UNION ALL
( 
SELECT 'mission' AS src
, m.one
, m.two
, m.buckle
, m.myshoe
FROM `bouhun`.`missions` m
WHERE m.`status` = 0
)
ORDER
BY src DESC
, CASE src WHEN 'teacher' THEN created_at END DESC
, CASE src WHEN 'mission' THEN RAND() END

---

*随访*

该问题已经过编辑，以更改第二个 SELECT 中的表名，使其现在与第一个 SELECT 中引用的表相同。

通过这种更改，我将只使用一个 SELECT。

并避免两组的UNION/UNION ALL。

我们可以在ORDER BY子句中添加一些巧妙的表达式。

像这样：

SELECT t.*
FROM `Teacher` t
WHERE t.status IN (0,1)
ORDER
BY t.status                              DESC
, IF( t.status=0 , t.created_at , NULL) DESC 
, IF( t.status=1 , RAND()       , NULL) DESC

Answer 2

不久前我遇到了类似的问题...... 这样的事情应该可以解决它。

SELECT * FROM
(
(SELECT ROW_NUMBER() OVER (order by `created_at` desc) AS RowNumber, `status`, id, datetime
FROM
`Teacher` AS `teacher`
WHERE
`teacher`.`status` = 1 ORDER BY `created_at` desc)
UNION 
(SELECT ROW_NUMBER() OVER (order by rand()) AS RowNumber, `status`, id, datetime
FROM
`Teacher` AS `teacher`
WHERE 
`teacher`.`status` = 0 ORDER By rand())
) order by `status`, RowNumber;

让我们根据给出的标准在表 1 和表 2 中添加一个行索引列(RowNumber)，以确保状态中的正确排序。 我们已经有了状态值为 (status的列。 然后只需选择*并按status排序，RowNumber将为您提供您真正需要的内容。 [请原谅我在查询中的语法错误。