Как указать родительское поле запроса из подзапроса в mySQL?

Есть ли способ указать родительское поле запроса из подзапроса в mySQL?

Например:
Я написал базовую программу типа бюллетеней в PHP.

В базе данных каждая запись содержит: id (PK) и parent_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" в качестве имени столбца, тогда мне нужно будет выполнить запрос, который возвращает me 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";

Неприятный способ работает отлично, но я чувствую возможность узнать что-то здесь, поэтому я подумал, что поставил бы вопрос.

Ответ 1

Как насчет:

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

Ответ 2

Вы можете попробовать что-то вроде этого

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

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

Ответ 3

Дайте таблицам уникальные имена:

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

Ответ 4

В 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

Ответ 5

Спасибо, Дон. У меня был вложенный запрос, как показано ниже, и предложение WHERE в нем не могло определить псевдоним 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 */
);

Итак, я просто добавил псевдоним 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 */
);

Надеюсь, что это будет полезно для кого-то.