php子查询语法,php-如何在mySQL的子查询中指定父查询字段？

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

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

例如：

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

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

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

$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”作为列名，那么我就必须执行一个返回 我两列具有相同的数据(对我来说似乎很混乱)

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

凌乱的方式很好用，但是我觉得有机会在这里学习一些东西，所以我想我应该提出这个问题。

5个解决方案

69 votes

怎么样：

$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上添加了别名，您可能会说：

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

Don answered 2020-02-12T18:46:56Z

4 votes

你可以尝试这样的事情

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

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

SELECT pt.id,

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

FROM post_table pt

WHERE pt.parent_id = 0

Adriaan Stander answered 2020-02-12T18:47:20Z

2 votes

为表指定唯一的名称：

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

Tatu Ulmanen answered 2020-02-12T18:47:41Z

1 votes

谢谢唐。 我有一个嵌套查询，如下所示，并且其中的v1子句无法确定别名JOIN。这是无效的代码：

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 */

);

因此，我再次在JOIN内部添加了别名v1。

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 */

);

希望这对某人有帮助。

Mr_Green answered 2020-02-12T18:48:09Z

0 votes

以下语法在Oracle中有效。 您可以测试在MySQL中是否也可以使用吗？在Oracle中称为标量子查询。

如果您两次使用同一张表，则只需要对这两个表使用不同的别名来区分它们。

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

Rajesh Chamarthi answered 2020-02-12T18:48:34Z