mysql - "Cannot used in global ORDER clause"与 mysql 排序

Question

我对这个 SQL 查询有疑问:

(SELECT tb1.id,tb1.bdate,tb1.jumpCard,tb1.publicImage,tb1.lastlogin
FROM users AS tb1, online AS tb2
WHERE tb1.valid='1' AND tb1.sex='female' AND tb1.looking_for='male' AND tb1.id = tb2.member_id
ORDER BY tb1.publicImage) ORDER BY tb1.id DESC

出于某种原因我得到:

Table 'tb1' from one of the SELECTs cannot be used in global ORDER clause

有什么建议吗？

谢谢

Answer 1

它不起作用的原因是外部 ORDER BY 不能“看到”tb1 - 它看到内部子查询的结果。因此，在语法正确的查询版本中，您只需 ORDER BY id :

(SELECT tb1.id,tb1.bdate,tb1.jumpCard,tb1.publicImage,tb1.lastlogin
FROM users AS tb1, online AS tb2
WHERE tb1.valid='1' AND tb1.sex='female' AND tb1.looking_for='male' AND tb1.id = tb2.member_id
ORDER BY tb1.publicImage) ORDER BY id DESC

但是，正如其他人所指出的，这可以更简单地编写为按 id 排序的单个查询