WYBIERANIE wielu kolumn za pomocą podzapytania

Próbuję wybrać 2 kolumny z podzapytania w następującym zapytaniu, ale nie mogę tego zrobić. Próbowałem utworzyć tabelę aliasów, ale nadal nie mogłem ich zdobyć.

SELECT
  DISTINCT petid,
  userid,
  (SELECT MAX(comDate) FROM comments WHERE petid=pet.id) AS lastComDate,
  (SELECT userid FROM comments WHERE petid=pet.id ORDER BY id DESC LIMIT 1) AS lastPosterID
FROM 
  pet LEFT JOIN comments ON pet.id = comments.petid
WHERE 
  userid='ABC'      AND 
  deviceID!='ABC'   AND 
  comDate>=DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 MONTH);

Zasadniczo próbuję uzyskać znak lastComDate& lastPosterIDz tego samego wiersza - wiersza, który jest najnowszy w komentarzach dla konkretnego zwierzaka. Proszę zasugerować, jak mogę je uzyskać w efektywny sposób.

Powyższe zapytanie działa, ale wydaje się przesadne, ponieważ ten sam wiersz jest pobierany dwukrotnie. Co więcej, ORDER BYklauzula jest znacznie wolniejsza niż funkcja agregująca - co znalazłem podczas profilowania zapytania. Docenione zostanie więc rozwiązanie pozwalające uniknąć sortowania.

— BufferStack
źródło

Jeśli masz indeks (petid, id) w tabeli komentarzy, kolejność według prawdopodobnie nie będzie powolna, ale przede wszystkim: wygląda na to, że twoje zapytanie dotyczy wszystkich zwierząt, w których skomentował identyfikator użytkownika „ABC” w ciągu ostatnich dwóch miesięcy, w których ID urządzenia nie jest „ABC” (choć nie jest jasne, w której tabeli ID urządzenia jest kolumna, prawdopodobnie zwierzęta domowe i ewentualnie komentarze) oraz kto był ostatnim komentatorem i datę ostatniego komentarza. Czy to prawda?

— Michael - sqlbot

@ Michael-sqlbot - Tak, właśnie to staram się zebrać. deviceIDJest od petsstołu - co oznacza, że po prostu nie dostać mi zwierzęta, które są przekazywane przez „abc” sam.

— BufferStack

Odpowiedzi:

SELECT DISTINCT petid, userid, lastComDate, lastPosterId
FROM 
    pet 
    LEFT JOIN comments ON pet.id = comments.petid 
    LEFT JOIN (
        SELECT MAX(comDate), userid, petid FROM comments GROUP BY userid
    ) a ON a.petid = pet.id
WHERE 
    userid='ABC' 
    AND deviceID!='ABC' 
    AND comDate>=DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 MONTH)
;

Możesz również wyciągnąć podzapytanie do tabeli tymczasowej, jeśli gdzieś po drodze wpłynie na wydajność.

— Walkiria
źródło

Próbowałem tego także wcześniej ... to zwraca NULLoba lastComDatei lastPosterIdwszystkie rekordy.

— BufferStack

Czy masz jakieś przykładowe dane?

— Walkiria

Jak podać przykładowe dane?

— BufferStack

Spróbuj podpowiedzi w tym poście: meta.stackexchange.com/questions/156729/...

— Valkyrie,

To dobrze, ale SQLFiddle jest lepszy;). Zobacz przykład tutaj . O wiele lepiej jest po prostu zobaczyć kod i dane źródłowe, a nie tabelę, którą trzeba sformatować.

— Marian,

Biorąc pod uwagę, że twoje stoły wyglądają tak:

create table pet (id int, userid int, deviceid int);
create table comments (id int, petid int, comdate date);

To zapytanie powinno załatwić sprawę:

SELECT 
        p.id, 
        p.userid,
        (SELECT MAX(comDate)
         FROM comments
         WHERE petid = p.id
         AND comDate >= DATE_SUB(
                 CURRENT_TIMESTAMP, INTERVAL 2 MONTH)
               ) AS lastComDate,
        (SELECT userid
         FROM comments
         WHERE petid = p.id
         AND comDate >= DATE_SUB(
              CURRENT_TIMESTAMP, INTERVAL 2 MONTH
         ) ORDER BY id DESC LIMIT 1) AS lastPosterID
    FROM 
        pet p

    WHERE 
        p.userid=1
        AND p.deviceID!=1

— druzin
źródło