mysql – 将字段值传递给子查询

在这种情况下,我通过子查询加入并希望将deptid传递给已连接的子查询,但我得到’D未定义’错误消息.

SELECT * 
    FROM(   
        SELECT D.name AS deptname,D.id AS deptid,WT.sortposition AS deptsortposition 
        FROM departments D JOIN web_taxonomy WT ON (WT.deptid=D.id AND WT.classid=0) 
        WHERE D.web=1
        ORDER BY sortposition
    ) AS D
    LEFT JOIN (
        SELECT C.name AS classname,C. id AS classid,C.department,WT.sortposition AS classsortposition,WT.deptid
        FROM classes C
        JOIN web_taxonomy WT ON (WT.classid=C.id AND WT.subclassid=0 AND WT.deptid=D.deptid) 
        WHERE web=1 ORDER BY classsortposition  
    ) AS C ON (C.department=D.deptid)

有没有什么方法可以传入类似于我在上面的强标签中包含的内容？

编辑：我犯了一个错误,最初留在工作查询中,只是添加了我想要运行的部分.本质上,我想通过仅获取与D子查询表中找到的共享相同deptid的行来最小化连接子查询的大小.

最佳答案

您不能在别名的子查询中使用别名“D”.

这应该工作(在第一个子查询中只使用X而不是D-并非严格必要但有助于提高可读性 – 并将对D的引用移动到第二个子查询之外)：

SELECT * 
    FROM(   
        SELECT 
           X.name AS deptname,X.id AS deptid,WT.sortposition AS deptsortposition 
        FROM departments X 
        JOIN web_taxonomy WT ON (WT.deptid=X.id AND WT.classid=0) 
        WHERE X.web=1
        ORDER BY sortposition
    ) AS D  -- this is available to objects referencing this alias
    LEFT JOIN (
        SELECT 
           C.name AS classname,WT.deptid
        FROM classes C JOIN web_taxonomy WT 
        ON WT.classid=C.id AND WT.subclassid=0
        WHERE web=1 ORDER BY classsortposition  
    ) AS C ON C.department=D.deptid AND C.deptid = D.deptid -- i.e. here

mysql – 将字段值传递给子查询

猜你在找的MySQL相关文章