mysql subquery field application Range

Here is my code:

SELECT field1, field2, 
       (SELECT * FROM table1 WHERE a = field2), 
       (SELE开发者_运维知识库CT COUNT(*) 
          FROM (SELECT * 
                  FROM table2 
                 WHERE c = field2) as t1) as count 
  FROM table3; 

...and here is error message:

/* SQL Error (1054): Unknown column 'field2' in 'where clause' */

I want to execute one query, to get table2's total counts.

The problem is that you're trying to use a variable declared in the outer query in the inner query. Variables are scoped the opposite way in SQL, you only have access to inner queries. What you want to do (I believe) is look for the number of table2 that have a c that matches the a from table1. This should give you that answer.

SELECT
    table1.a,
    table2.c,
    count(*)
FROM
    table1
JOIN
    table2 ON table2.c = table1.a
GROUP BY
    table1.a

I re-wrote your query as:

   SELECT t3.field1, 
          t3.field2,
          t1.*,
          t2.cnt
     FROM TABLE3 t3
LEFT JOIN TABLE1 t1 ON t1.a = t3.field2
LEFT JOIN (SELECT t.c,
                  COUNT(*) AS cnt
             FROM TABLE2 t
         GROUP BY t.c) t2 ON t2.c = t3.field2

The 1054 error is due to referencing field2 two subqueries deep - most only support one level deep.

UPDATE: I want to edit the answer, since in MySQL 8.0 your query is already correct, derived tables can access external references no matter the level.

SELECT field1, field2, 
       (SELECT 'hola' FROM table1 WHERE a = field2), 
       (SELECT COUNT(*) 
          FROM (SELECT * 
                  FROM table2 
                 WHERE c = field2) as t1) as count 
FROM table3; 

"Prior to MySQL 8.0.14, a derived table cannot contain outer references. This is a MySQL restriction that is lifted in MySQL 8.0.14, not a restriction of the SQL standard. For example, the derived table dt in the following query contains a reference t1.b to the table t1 in the outer query:" https://dev.mysql.com/doc/refman/8.0/en/derived-tables.html