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