php 如何从 mySQL 的子查询中指定父查询字段？

How to specify the parent query field from within a subquery in mySQL?

提问by justinl

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

有没有办法从 mySQL 的子查询中指定父查询字段？

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

例如：
我用 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.

在数据库中，每个帖子包含：id(PK) 和 parent_id(父帖子的 id)。如果帖子本身是父级，则其 parent_id 设置为 0。

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

我正在尝试编写一个 mySQL 查询，该查询将查找每个父帖子和父拥有的孩子的数量。

$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 iddoesn't know that it should be referring to the second idthat 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)

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

$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.

凌乱的方式工作正常，但我觉得有机会在这里学习一些东西，所以我想我会发布这个问题。

回答by Don

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:

或者如果您在 p1.id 上放置别名，您可能会说：

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

回答by Adriaan Stander

You could try something like this

你可以试试这样的

SELECT  pt.id,
        CountTable.Cnt
FROM    post_table pt LEFT JOIN
        (
            SELECT  parent_id,
                    COUNT(1) Cnt
            FROM    post_table
            WHERE   parent_id <> 0
            GROUP BY parent_id
        ) CountTable ON pt.id = CountTable.parent_id
WHERE   pt.parent_id = 0

To get back to your example, use the aliasof the main table in the sub select

要回到您的示例，请在子选择中使用主表的别名

SELECT  pt.id,
        (SELECT COUNT(1) FROM post_table WHERE parent_id = pt.id) 
FROM    post_table pt
WHERE   pt.parent_id = 0

回答by Tatu Ulmanen

Give the tables unique names:

为表指定唯一名称：

$query = "SELECT a.id, (SELECT COUNT(1) FROM post_table b WHERE parent_id = a.id) as num_children FROM post_table a WHERE a.parent_id = 0";

回答by Rajesh Chamarthi

The following syntax works in Oracle. Can you test if the same works in MYSQL too? It is called scalar subquery in Oracle.

以下语法适用于 Oracle。你能测试一下它是否也适用于 MYSQL 吗？在 Oracle 中称为标量子查询。

You would just need to alias the two tables differently to distinguish between them if you are using the same table twice.

如果您两次使用同一个表，您只需要为这两个表设置不同的别名即可区分它们。

sql> select empno,
  2         (select dname from dept where deptno = emp.deptno) dname
  3    from emp 
  4    where empno = 7369;

     EMPNO DNAME
---------- --------------
      7369 RESEARCH

sql> select parent.empno,
  2         (select mgr from emp where empno = parent.empno) mgr
  3    from emp parent
  4    where empno = 7876;

     EMPNO        MGR
---------- ----------
      7876       7788

回答by Mr_Green

Thanks Don. I had a nested query as shown below and a WHEREclause in it wasn't able to determine alias v1. Here is the code which isn't working:

谢谢唐。我有一个如下所示的嵌套查询，其中的WHERE子句无法确定 alias v1。这是不起作用的代码：

Select 
    teamid,
    teamname
FROM
    team as t1
INNER JOIN (
    SELECT 
        venue_id, 
        venue_scores, 
        venue_name 
    FROM venue 
    WHERE venue_scores = (
        SELECT 
            MAX(venue_scores) 
        FROM venue as v2 
        WHERE v2.venue_id = v1.venue_id      /* this where clause wasn't working */
    ) as v1    /* v1 alias already present here */
);

So, I just added the alias v1again inside the JOIN. Which made it work.

所以，我只是v1在JOIN. 这使它起作用。

Select 
    teamid,
    teamname
FROM
    team as t1
INNER JOIN (
    SELECT 
        venue_id, 
        venue_scores, 
        venue_name 
    FROM venue as v1              /* added alias v1 here again */
    WHERE venue_scores = (
        SELECT 
            MAX(venue_scores) 
        FROM venue as v2 
        WHERE v2.venue_id = v1.venue_id   /* Now this works!! */
    ) as v1     /* v1 alias already present here */
);

Hope this will be helpful for someone.

希望这对某人有帮助。

回答by M. Hamza Rajput

Parent query field within a subquery in MySQL 8.

MySQL 8 中子查询中的父查询字段。

I'm selecing games scores on the basis of username from tblgamescores using nested query.

我正在使用嵌套查询根据 tblgamescores 中的用户名选择游戏分数。

SELECT 
    GameScoresID, 
    (SELECT Username FROM tblaccounts WHERE AccountID = FromAccountID) AS FromUsername, 
    (SELECT Username FROM tblaccounts WHERE AccountID = ToAccountID) AS ToUsername,
    (SELECT Username FROM tblaccounts WHERE AccountID = WinAccountID) AS WinUsername,
    (SELECT Username FROM tblaccounts WHERE AccountID = LossAccountID) AS LossUsername,
    FromUserScore,
    ToUserScore 
FROM tblgamescores a 
WHERE FromAccountID = (SELECT AccountID FROM tblaccounts WHERE Username = "MHamzaRajput");