sql - 子查询的追加字段

我有一个相当复杂的查询，它基于子查询对结果进行排序。这很管用。查询如下。

 SELECT enrollments.*, users.*
 FROM enrollments
 INNER JOIN users
 ON enrollments.user_id = users.id
 WHERE enrollments.preview = FALSE
 ORDER BY (
   SELECT COUNT(progress_tracker)
   FROM progress_tracker
   WHERE progress_tracker.enrollment_id = enrollments.id
   AND progress_tracker.completed = TRUE
 )

在这里，我从用户和注册表中选择字段。理想情况下，我希望sql结果中还有一个字段。此字段表示按sql排序的结果：

SELECT COUNT(progress_tracker)
FROM progress_tracker
WHERE progress_tracker.enrollment_id = enrollments.id
AND progress_tracker.completed = TRUE

是否可以从此查询创建一个字段并将其追加到每行的结果中？行的名称将适当地称为enrollment_progress_tracker_completed_count。如果是，我该怎么做？

最佳答案

将子查询添加到select中，然后使用它进行排序：

SELECT e.*, u.*,
       (SELECT COUNT(*)
        FROM progress_tracker pt
        WHERE pt.enrollment_id = e.id AND pt.completed = TRUE
       ) as newcol
FROM enrollments e INNER JOIN
     users u
     ON e.user_id = u.id
WHERE e.preview = FALSE
ORDER BY newcol;