子查询 where 子句中的未知列

2022-01-23 00:00:00 subquery mysql

我的 INNER JOIN 子查询的 where 子句有问题.我收到 M.idMembre 的未知列错误.我尝试使用表名而不是别名，但我遇到了同样的问题.我还尝试从子查询中删除 WHERE 子句，并在子查询之后的 ON 子句中添加此条件.但是，无论哪种方式，我都有同样的问题.我觉得这很明显我在这里失踪了.

I'm having a problem in the where clause of my INNER JOIN subquery. I'm receiving a unknown column error for M.idMembre. I've tried using the table name instead of the alias but I get the same issue. I've also tried removing the WHERE clause from the subquery and adding this condition in the ON clause after the subquery. However, I'm having the same issue either way. I feel it's something obvious I'm missing here. 

SELECT DISTINCT M.`idMembre` ,  `couponsTypes`.`maxCouponType` 
FROM membres AS  `M` 
INNER JOIN (
SELECT idMembre, MAX( coupons.`idType` ) AS  `maxCouponType` 
FROM coupons
WHERE coupons.`idMembre` = M.`idMembre` 
GROUP BY idMembre
) AS  `couponsTypes` 
ON M.`idMembre` = couponsTypes.`idMembre`
ORDER BY maxCouponType DESC

如果您需要更多信息，请告诉我.

Let me know if you need more information.

推荐答案

不允许在连接子句的子查询中引用外部表.解决此问题的一种方法是根据连接条件在子查询中执行 group by:

You are not allowed to reference outer tables in a subquery in a join clause. One way to solve this is by doing a group by in the subquery based on the join condition:

SELECT DISTINCT M.`idMembre`, `couponsTypes`.`maxCouponType`
FROM membres AS `M` 
INNER JOIN
(SELECT idMembre, MAX(coupons.`idType`) AS `maxCouponType`
   FROM coupons
   GROUP BY idmembre
) `couponsTypes`
ON couponstypes.idMembre = M.idMember
ORDER BY maxCouponType DESC

但是，您根本不需要 membres 表.虽然在外层select中引用，但相当于coupons type表中的member id.因此，您可以将查询编写为:

But, you don't need the membres table at all. Although referenced in the outer select, it is equivalent to the member id in the coupons type table. So, you can write your query as:

SELECT idMembre, MAX(coupons.`idType`) AS `maxCouponType`
FROM coupons
GROUP BY idmembre
ORDER BY 2 DESC

这可能是最简单、最有效的表述方式了.

This is probably the simplest and most efficient way formulation.

相关文章