无效的 SQL 查询

发布于 2024-09-05 03:54:16 字数 914 浏览 13 评论 0原文

我有下一个查询，我认为这是一个有效的查询，但我不断收到错误，告诉我“WHERE em.p4 = ue.p3”存在问题 - Unknown columns 'ue.p3' in ' where 子句'。

这是查询：

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

如果我执行 EXISTS 函数内的子查询，一切正常，

请帮忙！

原文

I have the next query that in my opinion is a valid one, but I keep getting error telling me that there is a proble on "WHERE em.p4 = ue.p3" - Unknown column 'ue.p3' in 'where clause'.

This is the query:

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

If I execute the sub-query which is inside the EXISTS function, everything is O.K.

PLEASE HELP!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

揽清风入怀 2024-09-12 03:54:16

错误的原因是关联时只能引用一个子查询层。查看 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

EXISTS 如果满足则返回 true - 它不会根据返回“true”的子查询进行评估。不需要您拥有的额外子查询（无论如何这都会引起问题）。

The reason for the error is that you can only reference one subquery layer down when correlating. Look at where the ue alias is defined, and count the number of FROM clauses until to you reach the next reference.

I re-wrote your query as:

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

EXISTS returns true if satisfied -- it doesn't evaluate based on the subquery returning "true". There's no need for the additional subquery you have (which was causing problems anyway).

回复收藏 0 原文