MySQL 组合 2 个查询 - 内连接问题

Question

我正在尝试将子查询添加到选择中，如下所示:

select ProductID,
       ProductName,
       concat((UnitsInStock / (select sum(UnitsInStock) from products))*100, '%')
       as Percent_of_total_units_in_stock
from products
order by ProductID;

/*
This query returns the same result as query above.
Here 3119 is hardcoded whereas query above uses
subquery to calculate 3119 on the fly.
*/
select ProductID,
       ProductName,
       concat((UnitsInStock / 3119)*100, '%')
       as Percent_of_total_units_in_stock
from products
order by ProductID;

除了以“--”开头的 3 行之外，查询运行良好。注意:这只是为了表明问题 - 不会将其包含在实时查询中。

我隐约明白我正在尝试使用左连接运行内部查询，但这不起作用。如果这能向我更好地解释那就太好了。

问题:有没有办法构建这个查询，以便我可以同时运行这两个查询？我尝试了几种配置，但总是收到 MySQL 错误:

Unknown column 'a.receiver' in 'on clause'
Error code 1054.

谢谢

Answer 1

我认为您在子查询中引用了错误的别名。注释代码指出:

FROM messages AS aa
JOIN users AS bb ON aa.sender=bb.userid
LEFT JOIN blocked AS cc ON cc.requester=aa.sender AND cc.receiver = a.receiver
LEFT JOIN blocked AS dd ON dd.receiver=aa.receiver AND dd.requester = a.receiver

子查询中的messages表具有别名aa。您在输出查询中使用 a 作为别名。所以我认为这是一个错误，应该是这样的:

FROM messages AS aa
JOIN users AS bb ON aa.sender=bb.userid
LEFT JOIN blocked AS cc ON cc.requester=aa.sender AND cc.receiver = aa.receiver
LEFT JOIN blocked AS dd ON dd.receiver=aa.receiver AND dd.requester = aa.receiver