如何在MySQL的子查询中指定父查询字段? [英] How to specify the parent query field from within a subquery in mySQL?

问题描述

是否可以从mySQL的子查询中指定父查询字段?

例如:
我已经用PHP编写了一个基本的公告板类型程序.

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

在数据库中，每个帖子都包含:id(PK)和parent_id(父帖子的ID).如果帖子本身是父项，则将其parent_id设置为0.

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.

我正在尝试编写一个mySQL查询，该查询将查找每个父级帖子以及该父级拥有的子级数.

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";

棘手的是，第一个 id 不知道它应该引用子查询之外的第二个 id .我知道我可以执行SELECT id AS id_tmp，然后在子查询中引用它，但是如果我还想返回id并保持"id"作为列名，那么我必须做一个返回的查询我2列具有相同数据(对我来说似乎很混乱)

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.

推荐答案

如何:

$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";

或者如果您在p1.id上添加别名，您可能会说:

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";

这篇关于如何在MySQL的子查询中指定父查询字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！