1054 - 子查詢中的未知列

我有 2 表 1 儲存關係使用者其他購買使用者的數據

路徑表

+-------------+---------------+-------------+
| ancestor_id | descendant_id | lenght       |
+-------------+---------------+-------------+
|           1 |             1 |           0 |
|           1 |             2 |           1 |
|           1 |             3 |           1 |
|           1 |             4 |           2 |
|           1 |             5 |           3 |
|           1 |             6 |           4 |
|           2 |             2 |           0 |
|           2 |             4 |           1 |
|           2 |             5 |           2 |
|           2 |             6 |           3 |
|           3 |             3 |           0 |
|           4 |             4 |           0 |
|           4 |             5 |           1 |
|           4 |             6 |           2 |
|           5 |             5 |           0 |
|           5 |             6 |           1 |
|           6 |             6 |           0 |
+-------------+---------------+-------------+

這是買表

+--------+--------+
| userid | amount |
+--------+--------+
|      2 |   2000 |
|      4 |   6000 |
|      6 |   7000 |
|      1 |   7000 |

算法 =

1- 為每個使用者在最後 1000 處為後代選擇和團購

2- 加入路徑表，後代 ID = 結果 step1

3- 再次為每個使用者在最後 1000 處為祖先選擇和團購

4- 必須獲得最小路徑長度除零根據在步驟 2 中加入祖先，最後有 1000 ，如果只有一個 path_length 設置為 null

這個mysql程式碼我需要一個比較path_length和有祖先的地方

SELECT a.* 
FROM
   ( SELECT userid 
      FROM webineh_user_buys 
     GROUP BY userid
     HAVING SUM(amount) >= 1000
   ) AS buys_d 

JOIN
   webineh_prefix_nodes_paths AS a 
   ON a.descendant_id = buys_d.userid

JOIN  
   (
       SELECT userid  
       FROM webineh_user_buys 
       GROUP BY userid
       HAVING SUM(amount) >= 1000
   ) AS buys_a on (a.ancestor_id = buys_a.userid )


 JOIN 
    ( SELECT descendant_id
           , MAX(path_length) path_length 
        FROM webineh_prefix_nodes_paths 
        where a.ancestor_id = ancestor_id
       GROUP
          BY descendant_id
    ) b
   ON b.descendant_id = a.descendant_id
  AND b.path_length = a.path_length

GROUP BY a.descendant_id, a.ancestor_id 

預期結果

+--------+--------++--------++------
| descendant | ancestor | path_length
+--------+--------++--------++-------
|     1      |   null   |    null
|     2      |   1      |      1 
|     4      |   2      |      1
|     6      |   4      |      2

但有錯誤請參閱“where 子句”中的**sqlfile** 未知列“a.ancestor_id”

嘗試這個

SELECT a.descendant_id
   , CASE a.path_length WHEN 0 THEN null ELSE a.ancestor_id END ancestor_id
   , CASE a.path_length WHEN 0 THEN null ELSE a.path_length END path_length
FROM webineh_prefix_nodes_paths AS a 
JOIN (
   SELECT descendant_id, MIN(CASE path_length WHEN 0 THEN 1000 ELSE path_length END) min_path
   FROM (
       SELECT a.* 
       FROM (
           SELECT userid  
           FROM webineh_user_buys 
           GROUP BY userid
           HAVING SUM(amount) >= 1000
           ) AS buys_d 
       JOIN
           webineh_prefix_nodes_paths AS a 
       ON a.descendant_id = buys_d.userid
       JOIN (
           SELECT userid  
           FROM webineh_user_buys 
           GROUP BY userid
           HAVING SUM(amount) >= 1000
           ) AS buys_a 
       ON a.ancestor_id = buys_a.userid 
       ) tmp2
   GROUP BY descendant_id 
   ) td
ON a.descendant_id = td.descendant_id
      AND a.path_length = CASE td.min_path WHEN 1000 THEN 0 ELSE td.min_path END ;

引用自：https://dba.stackexchange.com/questions/141657