子查询where子句中的未知列 [英] Unknown column in subquery where clause

问题描述

我的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.

推荐答案

不允许在join子句中的子查询中引用外部表.解决此问题的一种方法是根据联接条件在子查询中执行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中引用，但它等效于优惠券类型表中的会员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.

这篇关于子查询where子句中的未知列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！