MySql JOINS 和 Count() 函数 : #1054 - Unknown column 'T.parent' in 'on clause'

Question

这是我的查询:

SELECT A.term_id, 
       B.name, 
       counts
FROM wp_term_taxonomy A
LEFT JOIN (
SELECT COUNT( C.parent ) AS counts
FROM wp_term_taxonomy C
GROUP BY C.term_id
) AS T ON A.term_id = T.parent -- This line causes error
JOIN wp_terms B ON A.term_id = B.term_id
WHERE A.taxonomy =  'product_cat'
AND A.parent =0

运行此语法时出现此错误:

#1054 - Unknown column 'T.parent' in 'on clause'

如何使用 ON 正确引用我的子查询选择语句？

Answer 1

您不能使用不存在的列的 on 子句连接表(如果您没有在动态生成的表 T 中选择)

您应该在 t 表中添加一个父列，例如:

SELECT A.term_id, B.name, counts
FROM wp_term_taxonomy A
LEFT JOIN (
  SELECT C.term_id as parent, COUNT( C.parent ) AS counts
  FROM wp_term_taxonomy C
  GROUP BY C.term_id
) AS T ON A.term_id = T.parent /* This line causes error*/
JOIN wp_terms B ON A.term_id = B.term_id
WHERE A.taxonomy =  'product_cat'
AND A.parent =0