Top 1 z lewym złączeniem


95

Biorąc pod uwagę poniższe zapytanie, może istnieć wiele wierszy w dps_markers z tym samym kluczem znacznika, ale chcemy połączyć tylko z pierwszym. Jeśli wezmę to zapytanie i usunę górną 1 i ORDER BY, otrzymam wartość mbg.marker_value, ale działam tak, jak jest, zawsze zwraca wartość null

SELECT u.id, mbg.marker_value 
FROM dps_user u
LEFT JOIN 
    (SELECT TOP 1 m.marker_value, um.profile_id
     FROM dps_usr_markers um (NOLOCK)
         INNER JOIN dps_markers m (NOLOCK) 
             ON m.marker_id= um.marker_id AND 
                m.marker_key = 'moneyBackGuaranteeLength'
     ORDER BY m.creation_date
    ) MBG ON MBG.profile_id=u.id 
WHERE u.id = 'u162231993'

Odpowiedzi:


204

Użyj OUTER APPLY zamiast LEFT JOIN:

SELECT u.id, mbg.marker_value 
FROM dps_user u
OUTER APPLY 
    (SELECT TOP 1 m.marker_value, um.profile_id
     FROM dps_usr_markers um (NOLOCK)
         INNER JOIN dps_markers m (NOLOCK) 
             ON m.marker_id= um.marker_id AND 
                m.marker_key = 'moneyBackGuaranteeLength'
     WHERE um.profile_id=u.id 
     ORDER BY m.creation_date
    ) AS MBG
WHERE u.id = 'u162231993';

W przeciwieństwie do JOIN, APPLY umożliwia odwoływanie się do u.id wewnątrz zapytania wewnętrznego.


Dzięki @Remus, pomogło mi.
Sarthak Shah

3

Kluczem do debugowania takich sytuacji jest samodzielne uruchomienie podzapytania / widoku wbudowanego, aby zobaczyć, jaki jest wynik:

  SELECT TOP 1 
         dm.marker_value, 
         dum.profile_id
    FROM DPS_USR_MARKERS dum (NOLOCK)
    JOIN DPS_MARKERS dm (NOLOCK) ON dm.marker_id= dum.marker_id 
                                AND dm.marker_key = 'moneyBackGuaranteeLength'
ORDER BY dm.creation_date

Po uruchomieniu tego zobaczysz, że profile_idwartość nie jest zgodna z u.idwartością u162231993, co wyjaśniałoby, dlaczego jakiekolwiek mbgreferencje wróciłyby null(dzięki lewemu złączeniu; nie dostałbyś niczego, gdyby było to złączenie wewnętrzne).

Zakodowałeś się w kącie za pomocą TOP, ponieważ teraz musisz dostosować zapytanie, jeśli chcesz uruchomić je dla innych użytkowników. Lepszym podejściem byłoby:

   SELECT u.id, 
          x.marker_value 
     FROM DPS_USER u
LEFT JOIN (SELECT dum.profile_id,
                  dm.marker_value,
                  dm.creation_date
             FROM DPS_USR_MARKERS dum (NOLOCK)
             JOIN DPS_MARKERS dm (NOLOCK) ON dm.marker_id= dum.marker_id 
                                         AND dm.marker_key = 'moneyBackGuaranteeLength'
           ) x ON x.profile_id = u.id
     JOIN (SELECT dum.profile_id,
                  MAX(dm.creation_date) 'max_create_date'
             FROM DPS_USR_MARKERS dum (NOLOCK)
             JOIN DPS_MARKERS dm (NOLOCK) ON dm.marker_id= dum.marker_id 
                                         AND dm.marker_key = 'moneyBackGuaranteeLength'
         GROUP BY dum.profile_id) y ON y.profile_id = x.profile_id
                                   AND y.max_create_date = x.creation_date
    WHERE u.id = 'u162231993'

Dzięki temu możesz zmienić idwartość w whereklauzuli, aby sprawdzić rekordy dla dowolnego użytkownika w systemie.


2

Ponieważ TOP 1z uporządkowanego zapytania podrzędnego nie ma wtedy opcji profile_id = 'u162231993' Usuń where u.id = 'u162231993'i zobacz wyniki.

Uruchom zapytanie podrzędne osobno, aby zrozumieć, co się dzieje.


ok, myślę, że teraz rozumiem, co masz na myśli. nadal muszę być w stanie sprawić, by to zadziałało. Zasadniczo, tabela dps_markers może mieć więcej niż jeden wiersz, co powoduje błędy w zapytaniu zewnętrznym, którym musimy zapobiec.
dstarh

1

Damir ma rację,

Twoje podzapytanie musi zapewnić, że dps_user.id jest równe um.profile_id, w przeciwnym razie przejdzie do górnego wiersza, który może, ale prawdopodobnie nie będzie równy Twojemu identyfikatorowi 'u162231993'

Twoje zapytanie powinno wyglądać następująco:

SELECT u.id, mbg.marker_value 
FROM dps_user u
LEFT JOIN 
    (SELECT TOP 1 m.marker_value, um.profile_id
     FROM dps_usr_markers um (NOLOCK)
         INNER JOIN dps_markers m (NOLOCK) 
             ON m.marker_id= um.marker_id AND 
                m.marker_key = 'moneyBackGuaranteeLength'
     WHERE u.id = um.profile_id
     ORDER BY m.creation_date
    ) MBG ON MBG.profile_id=u.id 
WHERE u.id = 'u162231993'

1
Tak, właśnie spróbowałem, ale u.id nie jest widoczny w sub-select. Wieloczęściowy identyfikator „u.id” nie mógł być powiązany.
dstarh

1
Możesz umieścić WHERE um.profile_id = 'u162231993'w zapytaniu podrzędnym i WHERE mbg.marker_value IS NOT NULLna zewnątrz.
Damir Sudarevic

1
nie będę znać profile_id, będzie to z innego połączenia. Zostało to wyciągnięte z dużo większego zapytania
dstarh

1
cóż, użyj zmiennej, @SearchFor = 'u162231993'a następnie użyj jej w WHERElub opublikuj niektóre struktury danych i tabel, aby inne osoby mogły pomóc i wypróbować.
Damir Sudarevic
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.