Коррелированные подзапросы: как получить идентификатор внешнего запроса?

Question

У меня есть несколько таблиц:

Person
---------------------
IDPerson, Name

StickerTransaction
---------------------
IDTransac, IDPerson, IDSomeoneElse, NbStickersReceived, NbStickersGiven

Purchase
---------------------
IDPurchase, IDPerson, NbStickersBought

Я пытаюсь найти каждого человека, который никогда не совершал транзакцию или в настоящее время имеет 0 стикеров. Чтобы получить количество стикеров, которые есть у человека, вот формула:

NbStickersBought + NbStickersReceived - NbStickersGiven

Вот запрос, который я получил до сих пор. У меня проблема в том, что в подзапросах... у меня нет доступа к идентификатору человека.

Есть ли способ получить доступ к идентификатору человека или есть лучший способ написать этот запрос?

SELECT People.IDPerson, People.Name
FROM
(
    -- Selects people that never made a sticker transaction

    SELECT p.IDPerson, p.Name
    FROM Person p
    LEFT JOIN StickerTransaction sT
    ON p.IDPerson = sT.IDPerson
    WHERE sT.IDPerson IS NULL

    UNION

    -- Selects people that have currently 0 sticker

    SELECT p.IDPerson, p.Name
    FROM Person p
    WHERE 0 = (
                SELECT SUM(NbStickers) AS NbStickers
                FROM (

                       -- I do not have access to p.IDPerson here...

                       SELECT (sT.NbStickersReceived - sT.NbStickersGiven) AS NbStickers
                       FROM StickerTransaction sT
                       WHERE sT.IDPerson = p.IDPerson

                     UNION ALL

                       -- I do not have access to p.IDPerson here either...

                       SELECT pu.NbStickersBought AS NbStickers
                       FROM Purchase pu
                       WHERE pu.IDPerson = p.IDPerson
                     )
              )
) People

Answer 1

AFAIK, вы не можете сделать корреляцию в производную таблицу. Но вы можете переписать ваш запрос в некоррелированный подзапрос:

SELECT People.IDPerson, People.NAME
FROM
(
    -- Selects people that never made a sticker transaction

    SELECT p.IDPerson, p.NAME
    FROM Person p
    LEFT JOIN StickerTransaction sT
    ON p.IDPerson = sT.IDPerson
    WHERE sT.IDPerson IS NULL

    UNION

    -- Selects people that have currently 0 sticker

    SELECT p.IDPerson, p.NAME
    FROM Person p
    JOIN     (
                SELECT sT.IDPerson, SUM(NbStickers) AS NbStickers
                FROM (

                       SELECT (sT.NbStickersReceived - sT.NbStickersGiven) AS NbStickers
                       FROM StickerTransaction sT

                     UNION ALL

                       SELECT pu.NbStickersBought AS NbStickers
                       FROM Purchase pu
                     ) dt
                GROUP BY sT.IDPerson
                HAVING SUM(NbStickers) = 0
             ) sT
    ON sT.IDPerson = p.IDPerson

) People

Answer 2

SELECT People.IDPerson, People.Name
FROM Person 
where IDPerson not in 
   (select distinct IDPerson from StickerTransaction)
and IDPerson not in 
   (SELECT IDPerson from Purchase group by IDPerson having SUM(NbStickers) > 0)

Answer 3

Использование NOT EXISTS найти тех, кто никогда не покупал.

Используйте другой подзапрос, чтобы найти тех, у кого такое же количество NbStickersReceived, что и NbStickersGiven.

SELECT p.IDPerson, p.Name
FROM Person p
where not exists (select 1 from Purchase
                  where IDPerson = p.IDPerson)
   or 0 = (select sum(NbStickersReceived) - sum(NbStickersGiven)
           from StickerTransaction
           where IDPerson = p.IDPerson)

Возможно, вам нужно COALESCE вокруг второго подзапроса, чтобы обрабатывать людей без каких-либо стикер транзакций.

Answer 4

Вам нужно будет сделать ссылку во внешнем утверждении.

-- Selects people that have currently 0 sticker
SELECT p.IDPerson, p.Name
 FROM Person p,
 (SELECT sT.IDPerson, SUM(NbStickers) sum_stickers
    FROM ((SELECT sT.IDPerson, (sT.NbStickersReceived - sT.NbStickersGiven) AS NbStickers
 FROM StickerTransaction sT)
UNION
(SELECT pu.IDPerson, pu.NbStickersBought AS NbStickers
  FROM PURCHASE pu
  ))
 group by sT.IDPerson
) zeroSticker
where zeroSticker.IDPerson = p.IDPerson and zeroSticker.sum_stickers = 0

Потому что в простом SQL нет возможности вставить параметр в подзапрос.