Order子查询与order by

Question

我需要为我的子查询字段订购我的查询。

企图：

 SELECT * from
 (
    SELECT *
    FROM (`multicategoria`)
    INNER JOIN `modelo` ON `modelo`.`id_MOD` = `multicategoria`.`id_MOD`
    INNER JOIN `categoria` ON `categoria`.`id_CAT` = `multicategoria`.`id_CAT`
    LEFT JOIN `media` ON `media`.`id_MED` = `modelo`.`id_MED`
    WHERE `categoria`.`id_CAT` =  '154'
    LIMIT 20, 4 )
order by x.id_CAT

但是mysql输出：

(1248)：每个派生表必须有自己的别名

Answer 1

x是什么？子查询中缺少的别名？

去掉子查询：

SELECT *
    FROM ...
    JOIN ...  ON ...
    JOIN ...  ON ...
    WHERE ...
    ORDER BY ...   -- Belongs after WHERE and before LIMIT
    LIMIT ...;     -- Useless without ORDER BY

问题:由于您为id_CAT指定了一个值，所以这对ORDER BY id_CAT没有意义。也许你想要ORDER BY id_CAT, something_else？或者简单地说是ORDER BY something_else？

Answer 2

只需将内表命名为：

SELECT * 
from ( 
    SELECT * 
    FROM (multicategoria) 
    INNER JOIN modelo ON modelo.id_MOD = multicategoria.id_MOD 
    INNER JOIN categoria ON categoria.id_CAT = multicategoria.id_CAT 
    LEFT JOIN media ON media.id_MED = modelo.id_MED 
    WHERE categoria.id_CAT = '154' LIMIT 20, 4 ) 
as TABLE_ALIAS  order by x.id_CAT;