无效SQL查询

Question

我有下一个疑问，在我看来是有效的，但我不断地收到错误，告诉我在"WHERE em.p4 = ue.p3“- 'where子句‘中的未知列'ue.p3’上有一个问题。

这是一个查询：

SELECT DISTINCT ue.p3
FROM 
    table1 AS ue INNER JOIN table2 AS e 
    ON ue.p3 = e.p3 
    WHERE 
        EXISTS(
            SELECT 1 FROM (
                SELECT (COUNT(*) >= 1) AS MinMutual 
                FROM table4 AS smm 
                WHERE 
                    smm.p1 IN (
                        SELECT sem.p3 FROM table3 AS sem 
                        INNER JOIN table2 AS em ON sem.p3 = em.p3 
                        WHERE em.p4 = ue.p3 AND 
                        sem.type = 'friends' AND em.p2 = 'normal' ) AND 
                    smm.p5 IN (
                        15000,15151
                    )
            ) AS Mutual WHERE 
        Mutual.MinMutual = TRUE) LIMIT 11

如果我执行存在函数中的子查询，那么一切都是正常的。

请帮帮忙！

Answer 1

造成此错误的原因是，关联时只能引用一个子查询层。查看定义ue别名的位置，并计数FROM子句的数量，直到到达下一个引用为止。

我将您的查询改写为：

SELECT DISTINCT ue.p3
  FROM table1 AS ue 
  JOIN table2 AS e ON ue.p3 = e.p3 
 WHERE EXISTS(SELECT 1 AS MinMutual 
                FROM table4 AS smm 
                JOIN TABLE3 sem ON sem.p3 = smm.p1
                               AND sem.type = 'friends'
                JOIN TABLE2 em ON em.p3 = sem.p3
                              AND em.p3 = ue.p3
                              AND em.p2 = 'normal'
               WHERE smm.p5 IN (15000,15151)
            GROUP BY ? --needs a group by clause, in order to use HAVING
              HAVING COUNT(*) >= 1)  
 LIMIT 11

如果满足，它将返回true --它不基于返回"true“的子查询进行计算。没有必要使用您所拥有的附加子查询(不管怎么说，这都会导致问题)。

Answer 2

AFAIK，这种关联查询到目前为止还不能在mysql中实现。联接到派生表，而不是使用现有表。