Ссылки на таблицы внешнего запроса в подзапросе

Question

Можно ли ссылаться на внешний запрос в подзапросе с MySQL? Я знаю, что в некоторых случаях это возможно:

SELECT *
FROM table t1
WHERE t1.date = (
    SELECT MAX(date)
    FROM table t2
    WHERE t2.id = t1.id
);

Но мне интересно, может ли что-то подобное сработать:

SELECT u.username, c._postCount
FROM User u
INNER JOIN (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    --# This is the reference I would need:
    WHERE p.user = u.id
    GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';

Я знаю, что могу добиться того же, используя GROUP BY или потянув внешний WHERE предложение в подзапрос, но мне нужно это для автоматической генерации SQL и не может использовать ни одну из альтернатив по различным другим причинам.

ОБНОВЛЕНИЕ: Извините, вопрос привел к некоторой путанице: первый запрос - просто рабочий пример, чтобы продемонстрировать то, что мне не нужно.

ОБНОВЛЕНИЕ 2: мне нужны оба сравнения u.id = p.user: первый подсчитывает количество пользователей, присоединившихся до '2009-10-10', а второй - условие соединения, которое правильно связывает строки таблицы.

Answer 1

Я думаю, что это не сработает, потому что вы ссылаетесь на свою производную таблицу 'c' как часть объединения.

Тем не менее, вы могли бы просто вынуть WHERE p.user = u.id хотя и заменить на GROUP BY p.user в производной таблице, потому что ON c.user = u.id будет иметь тот же эффект.

Answer 2

Разве это не то, что вы после?

SELECT u.username, c._postCount
FROM User u
INNER JOIN (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    GROUP BY p.user    
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';

Причина, по которой это будет работать, заключается в том, что природа самого соединения будет фильтровать пользователя. Вам не нужно, чтобы предложение WHERE явно фильтровало пользователя.

Answer 3

Можно ли ссылаться на внешний запрос в подзапросе с MySQL?

Да, это определенно возможно. MySQL 8.0.14 и выше:

13.2.11.9 Боковые производные таблицы

Производная таблица обычно не может ссылаться (зависеть) на столбцы предшествующих таблиц в том же предложении FROM. Начиная с MySQL 8.0.14 производная таблица может быть определена как боковая производная таблица, чтобы указать, что такие ссылки разрешены.

      SELECT u.username, c._postCount
FROM User u,
LATERAL (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    --# This is the reference I would need:
    WHERE p.user = u.id  
    GROUP BY p.user
) c
WHERE u.joinDate < '2009-10-10';

И уменьшенная версия (удаление ненужной группировки):

      SELECT u.username, c._postCount
FROM User u,
LATERAL (
    SELECT COUNT(*) AS _postCount
    FROM Posting p
    WHERE p.user = u.id  
) c
WHERE u.joinDate < '2009-10-10';

Связанное чтение: CROSS/OUTER APPLY в MySQL

Answer 4

Это, вероятно, лучше:

SELECT u.username,
(SELECT COUNT(*) FROM Posting WHERE user = u.id) as _postCount
FROM User u WHERE u.joinDate < '2009-10-10';

Answer 5

Это решение для postgresql. Вы можете использовать LATERAL JOIN, который доступен в postgresql. Вот как вы можете использовать его в своем запросе.

SELECT u.username, c._postCount
FROM User u
INNER JOIN LATERAL (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    WHERE p.user = u.id
    GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';

Вот ссылка, которую вы могли бы использовать. https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df

Answer 6

Очень близко...

WHERE t1.date = ( 
    SELECT MAX(date) 

изменить на

WHERE t1.date IN ( 
    SELECT MAX(date) 

Так как ваш запрос выполняет MAX(), он всегда будет возвращать только одну дату..., так как ваш подвыбор имеет фильтр по уникальному идентификатору, он должен дать вам то, что вы хотите.

Answer 7

Вот как вы это делаете, чтобы расширить принятый ответ

      SELECT u.username, c._postCount
FROM User u
INNER JOIN (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    --# This is the reference I would need:
    --WHERE p.user = u.id ####REMOVE THIS####
    GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10'
AND c.user = u.id -- ####ADD THIS####

Answer 8

Это будет работать нормально

SELECT u.id as userid,u.username, c._postCount
FROM User u
INNER JOIN (
    SELECT p.user, COUNT(*) AS _postCount
    FROM Posting p
    --# This is the reference I would need:
    WHERE p.user = userid
    GROUP BY p.user
) c ON c.user = u.id
WHERE u.joinDate < '2009-10-10';