Как указать родительское поле запроса из подзапроса в 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 */
);
Надеюсь, что это будет полезно для кого-то.
