MySQL union 和 order by help

问题描述

以下代码有效，但是当我将 Order by id 更改为 Order by s.id 时，出现此错误

The below code works but when i change Order by id to Order by s.id, i get this error

'order 子句'中的未知列's.id'

$construct =  "SELECT child.* FROM products child LEFT JOIN products parent on parent.name=child.parent INNER JOIN subscribe s ON (s.productid = parent.id) WHERE s.username='$logged' AND s.type='0'
        UNION
        SELECT child.* FROM products child LEFT JOIN products parent on parent.sid=child.sid INNER JOIN subscribe s ON (s.productid = parent.id) WHERE s.username='$logged' AND parent.keyword != child.name AND s.type='1'
        ORDER BY s.id DESC";

如何更改代码使其按 s.id 排序，即订阅表的 id?

How can i change the code so it is ordered by s.id, which is the subscribe table's id?

推荐答案

MySQL 正在尝试将 ORDER BY 应用于 UNION，但 UNION 只有 child 列(没有 child. 前缀)，在 UNION 中没有 s.id .但是你可以添加一个:

MySQL is trying to apply the ORDER BY to the UNION but the UNION only has the child columns (without the child. prefix at that), there is no s.id in the UNION. But you can add one:

SELECT child.*, s.id as sid ...
UNION
SELECT child.*, s.id as sid ...
ORDER BY sid DESC

您需要给它一个别名，因为 UNION 会去掉表名或别名前缀.如果 child 中有 sid 列，则使用其他内容作为 s.id 的别名.

You need to give it an alias as the UNION will strip off the table name or alias prefix. If there is an sid column in child then use something else as the alias for s.id.

这篇关于MySQL union 和 order by help的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！