mysql子查询字段,如何在MySQL的子查询中指定父查询字段？

Is there a way to specify the parent query field from within a subquery in mySQL?

For Example:

I have written a basic Bulletin Board type program in PHP.

In the database each post contains: id(PK) and parent_id(the id of the parent post). If the post is itself a parent, then its parent_id is set to 0.

I am trying to write a mySQL query that will find every parent post and the number of children that the parent has.

$query = "SELECT id, (

SELECT COUNT(1)

FROM post_table

WHERE parent_id = id

) as num_children

FROM post_table

WHERE parent_id = 0";

The tricky part is that the first id doesn't know that it should be referring to the second id that is outside of the subquery. I know that I can do SELECT id AS id_tmp and then refer to it inside the subquery, but then if I want to also return the id and keep "id" as the column name, then I'd have to do a query that returns me 2 columns with the same data (which seems messy to me)

$query = "SELECT id, id AS id_tmp,

(SELECT COUNT(1)

FROM post_table

WHERE parent_id = id_tmp) as num_children

FROM post_table

WHERE parent_id = 0";

The messy way works fine, but I feel an opportunity to learn something here so I thought I'd post the question.

解决方案

How about:

$query = "SELECT p1.id,

(SELECT COUNT(1)

FROM post_table p2

WHERE p2.parent_id = p1.id) as num_children

FROM post_table p1

WHERE p1.parent_id = 0";

or if you put an alias on the p1.id, you might say:

$query = "SELECT p1.id as p1_id,

(SELECT COUNT(1)

FROM post_table p2

WHERE p2.parent_id = p1.id) as num_children

FROM post_table p1

WHERE p1.parent_id = 0";