mysql - 子查询中的未知列

Question

我收到此错误:

SET @t_rev := 0.0; 

SET @t_margin := 0.0; 

SET @j_hrs := 0.0; 

SET @t_exp := 0.0; 

SET @j_id := 0; 

SELECT q.quote_id, 
       c.company_name, 
       j.create_date, 
       j.direct_labour_rate, 
       j.overhead_labour_rate, 
       @j_id := j.job_number                                     AS job_number, 
       @j_hrs := (SELECT Sum(Time_to_sec(IF(( end_date <> '00:00:00' 
                                              AND NOT Isnull(end_date) ), 
                                                   Timediff(Concat(end_date, ' ' 
                                                            , 
                                                            end_time), 
                                                   Concat(create_date, ' ', 
                                                   start_time 
                                                   )), Timediff 
                                                    (Concat(create_date, ' ', 
                                                     end_time 
                                                     ), 
                                         Concat(create_date, ' ', start_time) 
                                         ))) / 3600) AS job_time 
                  FROM   production_master 
                  WHERE  job_number = j.job_number)              AS j_time, 
       @t_exp := ( Ifnull((SELECT Sum(amount) 
                           FROM   job_expenses 
                           WHERE  job_id = j.job_number), 0.0) ) AS t_exp, 
       @t_rev := (SELECT Sum(t_rev2) 
                  FROM   (SELECT amount AS t_rev2 
                          FROM   ready_to_ship_detail 
                          WHERE  `job_id` = j.job_number 
                          GROUP  BY invoice_number) AS t_sum)    AS t_rev, 
       @t_margin := ( @t_rev / ( ( ( j.direct_labour_rate 
                                     + j.overhead_labour_rate ) * ( @j_hrs ) ) + 
                                 ( 
                                 @t_exp ) ) )                    AS margin 
FROM   quote_master q 
       LEFT JOIN create_job_master j 
              ON j.quote_id = q.quote_id 
       LEFT JOIN company_master c 
              ON q.company_id = c.company_id 
WHERE  q.create_date BETWEEN '2016-04-01' AND '2016-04-10' 
ORDER  BY c.company_name ASC, 
          q.quote_id DESC 

在@t_rev = ...行上，我有j.job_number，其结果为'where子句'中的未知列'j.job_number' 当我执行查询时。

我尝试了这个:@j_id = j.job_number 并在子查询中调用job_id=@j_id，它不会抛出错误，但完整列显示相同值为 t_rev像这样

+-------+
| t_rev |
+-------+
|  236  |
|  236  |
|  236  |
|  236  |
+-------+

如何解决这个问题？

结果错误:“where 子句”中存在未知列“j.job_number”

Answer 1

您正在双重嵌套查询中访问别名j，但实际上无法访问它。

我稍微修改了您的 @t_rev 子查询(因为您没有使用分组列，因此不需要对其进行分组)

@t_rev := (SELECT SUM(amount) AS t_rev2 
              FROM   ready_to_ship_detail 
              WHERE  `job_id` = j.job_number) AS t_sum)    AS t_rev,