需要 mysql 查询帮助

Question

第一段背景信息：这是关于 cms，它在单个表中保存对象-对象关系及其排序，列是 object_id、parent_id 和排序顺序

第二段有带有多个连接的查询，我想按 2 个参数排序。其中之一是对象本身的排序，第二是其父对象的排序顺序。

我现在的疑问是：

SELECT obj_asset.*, object.headline AS title, oo.sort_order AS sort, op.sort_order FROM obj_asset 
JOIN object ON obj_asset.object_id = object.object_id 
JOIN object_object AS oo on obj_asset.object_id = oo.object_id
JOIN (SELECT sort_order from object_object WHERE object_id = (SELECT parent_id from object_object WHERE object_id = obj_asset.object_id )) AS op ON obj_asset.object_id = oo.object_id
WHERE obj_asset.profile_id = 140 AND obj_asset.rsvp_enabled = 1 AND object.is_published = 1 ORDER BY sort DESC;

它不起作用。但这工作正常：

SELECT obj_asset.*, object.headline AS title, oo.sort_order AS sort, op.sort_order FROM obj_asset 
JOIN object ON obj_asset.object_id = object.object_id 
JOIN object_object AS oo on obj_asset.object_id = oo.object_id
JOIN (SELECT sort_order from object_object WHERE object_id = (SELECT parent_id from object_object WHERE object_id = 11111 )) AS op ON obj_asset.object_id = oo.object_id
WHERE obj_asset.profile_id = 140 AND obj_asset.rsvp_enabled = 1 AND object.is_published = 1 ORDER BY sort DESC;

我得到的错误是：

1054 - “where 子句”中的未知列“obj_asset.object_id”

我怎样才能让它工作？

谢谢！

编辑：如果我能想出替代方法将父母排序到查询中，我可以解决这个问题。有这样的办法吗？

Answer 1

正如您已经发现的，问题在于您尝试在子查询的约束中使用外部查询中的列：

  (SELECT sort_order
      from object_object
      WHERE object_id = (SELECT parent_id
                         from object_object
                         WHERE object_id = obj_asset.object_id )
     )

可以重写此子查询，但仅查看原始查询时尚不清楚如何重写。

您的原始查询（已重新格式化）中可能存在错误：

SELECT obj_asset.*, object.headline AS title, oo.sort_order AS sort, op.sort_order
FROM obj_asset 
JOIN object ON obj_asset.object_id = object.object_id 
JOIN object_object AS oo on obj_asset.object_id = oo.object_id
JOIN (SELECT sort_order
      from object_object
      WHERE object_id = (SELECT parent_id
                         from object_object
                         WHERE object_id = obj_asset.object_id )
     ) AS op ON obj_asset.object_id = oo.object_id
WHERE obj_asset.profile_id = 140 AND obj_asset.rsvp_enabled = 1 AND object.is_published = 1 ORDER BY sort DESC;

名为 op 的子查询未在任何联接或 where 子句中使用。

我的最佳猜测是您想要执行以下操作：

SELECT obj_asset.*, object.headline AS title, oo.sort_order AS sort, op.sort_order
FROM obj_asset 
JOIN object ON obj_asset.object_id = object.object_id 
JOIN object_object AS oo on obj_asset.object_id = oo.object_id
JOIN (SELECT sort_order, o2.object_id
    from object_object as o1
    INNER JOIN object_object as o2 ON o1.object_id = o2.parent_id
    ) AS op ON obj_asset.object_id = op.object_id
WHERE obj_asset.profile_id = 140 AND obj_asset.rsvp_enabled = 1 AND object.is_published = 1 ORDER BY sort DESC;

Answer 2

我认为你需要输入这个（包括 obj_asset 到 FROM）：

(SELECT parent_id FROM object_object, obj_asset  WHERE object_object.object_id = obj_asset.object_id )

但不是这个：

(SELECT parent_id from object_object WHERE object_id = obj_asset.object_id )

Answer 3

您的子查询在 FROM 子句中不包含 obj_asset。

(SELECT parent_id FROM object_object WHERE object_id = obj_asset.object_id )

让这部分运行起来，你应该对整个事情有更好的运气。

只是一个提示...如果您在 SQL 语句中添加更多换行符和缩进，您将更容易发现问题。