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

mysql - pass field value into subquery

提问by Magic Lasso

In this case I am joining by a subquery and want to pass in the deptid to the joined subquery but im getting 'D is not defined' error message.

在这种情况下，我通过子查询加入并希望将 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)

Any way to pass in the reference similar to what I wrapped in the strong tags above?

有什么方法可以传递类似于我在上面的强标签中包裹的参考文献？

EDIT: I made a mistake and left in the working query originally and just added the part that I wanted to function. Essentially I want to minimize the size of the join subquery by already only getting the rows that share the same deptid as was found in the D subquery table.

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

回答by davek

You cannot use your alias "D" within the subquery you are aliasing.

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

This should work (just using X instead of D in the first subquery -not strictly necessary but helps with readability - and moving the reference to D to outside of the second subquery):

这应该可以工作（仅在第一个子查询中使用 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
           , 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
        WHERE web=1 ORDER BY classsortposition  
    ) AS C ON C.department=D.deptid AND C.deptid = D.deptid -- i.e. here

回答by Bill Karwin

I don't think you need to do subqueries at all:

我认为你根本不需要做子查询：

SELECT D.name AS deptname, D.id AS deptid, WT1.sortposition AS deptsortposition,
  C.name AS classname, C.id AS classid, C.department, 
  WT2.sortposition AS classsortposition, WT2.deptid
FROM departments AS D
JOIN web_taxonomy AS WT1 ON (WT1.deptid=D.id AND WT1.classid=0)
LEFT OUTER JOIN web_taxonomy AS WT2 ON (WT2.deptid=D.id AND WT2.subclassid=0)
LEFT OUTER JOIN classes AS C ON (C.id=WT2.classid AND C.department=WT2.deptid);

The joins to WT1 should benefit from indexes:

与 WT1 的连接应该受益于索引：

ALTER TABLE web_taxonomy
  ADD KEY wt_dept_class (deptid, classid),
  ADD KEY wt_dept_subclass (deptid, subclassid);

The join to classes uses that table's PRIMARY key index.

与类的连接使用该表的 PRIMARY 键索引。

Here's the output of EXPLAIN for this query:

以下是此查询的 EXPLAIN 输出：

+----+-------------+-------+--------+--------------------------+---------------+---------+------------------+------+-----------------------+
| id | select_type | table | type   | possible_keys            | key           | key_len | ref              | rows | Extra                 |
+----+-------------+-------+--------+--------------------------+---------------+---------+------------------+------+-----------------------+
|  1 | SIMPLE      | D     | ALL    | PRIMARY,id               | NULL          | NULL    | NULL             |    1 | NULL                  |
|  1 | SIMPLE      | WT1   | ref    | dept_class,dept_subclass | dept_class    | 10      | test.D.id,const  |    1 | Using index condition |
|  1 | SIMPLE      | WT2   | ref    | dept_class,dept_subclass | dept_subclass | 10      | test.D.id,const  |    1 | Using where           |
|  1 | SIMPLE      | C     | eq_ref | PRIMARY,id               | PRIMARY       | 8       | test.WT2.classid |    1 | Using where           |
+----+-------------+-------+--------+--------------------------+---------------+---------+------------------+------+-----------------------+

To tell the truth, I had to edit that EXPLAIN report a little bit to show that result. I tested with tables with zero rows, so the optimizer mistakenly chose the dept_class index for WT2. If you test with your real data, I think it'll choose the dept_subclass index correctly.

说实话，我不得不稍微编辑一下 EXPLAIN 报告以显示该结果。我用零行的表进行了测试，因此优化器错误地为 WT2 选择了 dept_class 索引。如果您使用真实数据进行测试，我认为它会正确选择 dept_subclass 索引。

---

---

I tried your query, with a small modification to get around the error on D.deptid:

我尝试了您的查询，并稍作修改以解决 D.deptid 上的错误：

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=C.department)
  WHERE web=1 ORDER BY classsortposition
) AS C ON (C.department=D.deptid);

And the EXPLAIN report:

和解释报告：

+----+-------------+------------+------+--------------------------+------------+---------+-----------------------------+------+----------------------------------------------------+
| id | select_type | table      | type | possible_keys            | key        | key_len | ref                         | rows | Extra                                              |
+----+-------------+------------+------+--------------------------+------------+---------+-----------------------------+------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL                     | NULL       | NULL    | NULL                        |    2 | NULL                                               |
|  1 | PRIMARY     | <derived3> | ALL  | NULL                     | NULL       | NULL    | NULL                        |    2 | Using where; Using join buffer (Block Nested Loop) |
|  3 | DERIVED     | C          | ALL  | PRIMARY,id               | NULL       | NULL    | NULL                        |    1 | Using where; Using temporary; Using filesort       |
|  3 | DERIVED     | WT         | ref  | dept_class,dept_subclass | dept_class | 10      | test.C.department,test.C.id |    1 | Using index condition; Using where                 |
|  2 | DERIVED     | D          | ALL  | PRIMARY,id               | NULL       | NULL    | NULL                        |    1 | Using where; Using temporary; Using filesort       |
|  2 | DERIVED     | WT         | ref  | dept_class,dept_subclass | dept_class | 10      | test.D.id,const             |    1 | Using index condition                              |
+----+-------------+------------+------+--------------------------+------------+---------+-----------------------------+------+----------------------------------------------------+

Yuck! It runs a tablescan for both departments and classes (that's the ALLin the type column), and it creates a temp table for each of the subqueries, and then joins them withoutany benefit of an index (that's what is meant by Using join buffer). Not a fun optimization plan to see.

糟糕！它为部门和类（即ALL类型列中的）运行表扫描，并为每个子查询创建一个临时表，然后在没有索引的情况下连接它们（这就是Using join buffer）。不是一个有趣的优化计划。

In general, sorting should be the last part of the SQL query. Don't try to sort subquery results to work around the optimizer. You want the join to be assisted by an index if possible, but the order of the best index for the join isn't necessarily the order you want the final result to be returned in. So let the optimizer do its work to do the join, and then sort the final result afterwards.

一般来说，排序应该是 SQL 查询的最后一部分。不要尝试对子查询结果进行排序以绕过优化器。如果可能，您希望连接由索引辅助，但连接的最佳索引的顺序不一定是您希望返回最终结果的顺序。因此，让优化器完成连接的工作，然后对最终结果进行排序。

SELECT D.name AS deptname, D.id AS deptid, WT1.sortposition AS deptsortposition,
  C.name AS classname, C.id AS classid, C.department,
  WT2.sortposition AS classsortposition, WT2.deptid
FROM departments AS D
JOIN web_taxonomy AS WT1 ON (WT1.deptid=D.id AND WT1.classid=0)
LEFT OUTER JOIN web_taxonomy AS WT2 ON (WT2.deptid=D.id AND WT2.subclassid=0)
LEFT OUTER JOIN classes AS C ON (C.id=WT2.classid AND C.department=WT2.deptid)
ORDER BY deptsortposition, classsortposition;

This still uses onetemp table & filesort, but not two. And it avoids the join buffer; each join is index-assisted.

这仍然使用一个临时表和文件排序，但不是两个。它避免了连接缓冲区；每个连接都是索引辅助的。

+----+-------------+-------+--------+--------------------------+---------------+---------+------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys            | key           | key_len | ref              | rows | Extra                           |
+----+-------------+-------+--------+--------------------------+---------------+---------+------------------+------+---------------------------------+
|  1 | SIMPLE      | D     | ALL    | PRIMARY,id               | NULL          | NULL    | NULL             |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | WT1   | ref    | dept_class,dept_subclass | dept_class    | 10      | test.D.id,const  |    1 | Using where; Using index        |
|  1 | SIMPLE      | WT2   | ref    | dept_subclass            | dept_subclass | 10      | test.D.id,const  |    1 | Using where                     |
|  1 | SIMPLE      | C     | eq_ref | PRIMARY,id               | PRIMARY       | 8       | test.WT2.classid |    1 | Using where                     |
+----+-------------+-------+--------+--------------------------+---------------+---------+------------------+------+---------------------------------+