假设您要对整个批次进行排序，请尝试在整个查询中用括号括起来，并在后面加上order by done：

select id, userid, status, timestart, timeend, courseid, enrolid, roleid from
((SELECT ue.id, ue.userid, ue.status, ue.timestart, ue.timeend, e.courseid,
        e.id AS enrolid, ra.roleid, u.firstname, u.lastname, c.fullname
 FROM user_enrolments ue
 JOIN enrol e ON e.id = ue.enrolid
 JOIN course c ON c.id = e.courseid
 JOIN user u ON u.id = ue.userid
 JOIN context ct ON ct.instanceid = c.id
 LEFT JOIN role_assignments ra ON ra.userid = u.id AND 
                               ra.contextid = ct.id AND
                               ra.itemid = e.id
 WHERE e.customint1 = 1 AND u.deleted = 0 AND 
       ct.contextlevel = 50 AND (ue.status = 0 OR ue.status = 1)) 

UNION

(SELECT de.enrolid AS id, de.userid, de.status, de.date_ini, de.date_fin, 
        de.courseid, de.enrolid, de.roleid, u.firstname, u.lastname, ' ' as fullname
 FROM deleted_enrols de
 JOIN user u ON u.id = de.userid
 WHERE userid = ANY (SELECT userid FROM local_users WHERE clientid = 1))) s1

ORDER BY firstname, lastname, fullname LIMIT 0, 100

（显然，第二个select语句中的fullname将被填充，但这似乎是合理的）

你的调查结果 UNION 不要包含表“u”中的任何字段，因此这些结果不能按表“u”字段排序。
您可以执行联合，然后将结果重新联接到表'u'，然后使用该表按表'u'字段对结果进行排序。排序时也存在类似的问题 course.fullname ，所以也需要重新连接。

SELECT x.id, x.userid, x.status, x.timestart, x.timeend, x.courseid, x.enrolid, x.roleid
FROM ((SELECT ue.id, ue.userid, ue.status, ue.timestart, ue.timeend, e.courseid,
        e.id AS enrolid, ra.roleid
       FROM user_enrolments ue
         JOIN enrol e ON e.id = ue.enrolid
         JOIN course c ON c.id = e.courseid
         JOIN user u ON u.id = ue.userid
         JOIN context ct ON ct.instanceid = c.id
         LEFT JOIN role_assignments ra ON ra.userid = u.id 
           AND ra.contextid = ct.id 
           AND ra.itemid = e.id
       WHERE e.customint1 = 1 AND u.deleted = 0 
           AND ct.contextlevel = 50 AND (ue.status = 0 OR ue.status = 1)) 
       UNION
       (SELECT de.enrolid AS id, de.userid, de.status, de.date_ini, de.date_fin, 
        de.courseid, de.enrolid, de.roleid
        FROM deleted_enrols de
          JOIN user u ON u.id = de.userid
         WHERE userid = ANY (SELECT userid FROM local_users WHERE clientid = 1))
) x
  JOIN user z ON z.id = x.userid
  JOIN course d ON d.id = x.courseid
ORDER BY z.firstname, z.lastname, d.fullname LIMIT 0, 100

正如mysql关于union的文档所说：
这种order by不能使用包含表名的列引用（即tbl\u name.col\u name格式的名称）。相反，请在第一个select语句中提供列别名，并在ORDERBY中引用别名(或者，使用列位置引用顺序中的列。但是，不推荐使用列位置。）
另外，如果要排序的列具有别名，order by子句必须引用别名，而不是列名。
因此，不要引用任何表名，也不要使用union结果集中的列。

您需要在联合查询的选择中包含要排序的数据；跟随工会的命令被当作 SELECT * FROM (unions) ORDER BY ... 因此，任何不来自工会的东西都不能用于订购。
具有讽刺意味的是，类似的查询是获得所需内容的关键，例如

SELECT x, y, z
FROM (
SELECT x, y, z, somethingIdontactuallywant
FROM blah
UNION
SELECT a, b, c, somethingIdontactuallywant
FROM blah2
) AS u
ORDER BY u.somethingIdontactuallywant