mysql - 在 MySQL 的 ORDER BY 中使用别名列

Question

我创建了一个 SQL 查询来对具有值的记录进行分组并按别名对记录进行排序，如下所示:

SELECT * 
FROM   `timeline`, 
       (SELECT Max(mycountview) 
        FROM   (SELECT Count(id_timeline) AS mycountview
                FROM   `timeline` 
                WHERE  type_timeline = '1' 
                GROUP  BY mediaid)AS mycountview)AS max_mycountview
WHERE  type_timeline = '1' 
GROUP  BY mediaid 
HAVING Count(id_timeline) < max_mycountview
ORDER  BY max_mycountview DESC 
LIMIT  0, 5 

但是我收到一个错误:

Unknown column 'max_mycountview' in 'order clause'

如何解决？

Answer 1

您需要将子查询移动到select 语句而不是from 子句:

SELECT *, 
        (SELECT Max(mycountview) 
        FROM   (SELECT Count(id_timeline) AS mycountview
                FROM   `timeline` 
                WHERE  type_timeline = '1' 
                GROUP  BY mediaid)AS mycountview) AS max_mycountview
FROM   `timeline`
WHERE  type_timeline = '1' 
GROUP  BY mediaid 
HAVING Count(id_timeline) < max_mycountview
ORDER  BY max_mycountview DESC 
LIMIT  0, 5 

---

实际上，在重新阅读您的查询后，我认为这不是最有效的方法。您的查询正在表之间执行 cross join。然而，子查询并不相关——它为每一列返回相同的值。在这种情况下，您只需要在子查询中定义列名，以便能够在 having 和 order by 子句中访问它:

SELECT * 
FROM   `timeline`,
       (SELECT Max(mycountview) max_mycountview   <!--Define Column Alias Here
        FROM   (SELECT Count(id_timeline) mycountview
                FROM   `timeline` 
                WHERE  type_timeline = '1' 
                GROUP  BY mediaid) t) y
WHERE  type_timeline = '1' 
GROUP  BY mediaid 
HAVING Count(id_timeline) < max_mycountview
ORDER  BY max_mycountview DESC 
LIMIT  0, 5