Top 1 z lewym łącznikiem

Biorąc pod uwagę poniższe zapytanie, w dps_markers może być wiele wierszy z tym samym kluczem znacznika, ale chcemy połączyć tylko z pierwszym. Jeśli wezmę to zapytanie i usunąć top 1 I zamówić przez i dostać wartość dla mbg.marker_value ale tak jak jest zawsze zwraca 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'
Author: dstarh, 2010-01-09
Source

4 answers

Użyj zewnętrznego zastosuj zamiast lewego połączenia: 

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 pozwala odwoływać się do u.id wewnątrz wewnętrznego zapytania.

 151
Author: Remus Rusanu,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-01-11 02:37:57

Kluczem do debugowania takich sytuacji jest uruchomienie widoku subquery/inline na własną rękę, aby zobaczyć, jakie jest wyjście: 

  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

Uruchamiając to, zauważysz, że wartość profile_id nie pasuje do {[3] } wartości u162231993, CO wyjaśniałoby, dlaczego jakiekolwiek referencje mbg zwracałyby null (dzięki lewemu połączeniu; nie otrzymałbyś niczego, gdyby było wewnętrznym połączeniem).

Zakodowałeś się w rogu używając TOP, ponieważ teraz musisz dostosować zapytanie, jeśli chcesz uruchomić it 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ć wartość id w klauzuli where, aby sprawdzić rekordy dla dowolnego użytkownika w systemie.

 2
Author: OMG Ponies,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-01-09 16:38:34

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

Uruchom pod-zapytanie oddzielnie, aby zrozumieć, co się dzieje.

 1
Author: Damir Sudarevic,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-01-09 14:54:34

Damir ma rację,

Twoje zapytanie podrzędne musi zapewnić, że dps_user.id równa się um.profile_id, w przeciwnym razie pobierze górny wiersz, który może, ale prawdopodobnie nie jest równy twojemu id 'u162231993'

Twoje zapytanie powinno wyglądać tak: 

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'
 0
Author: Nathan Koop,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-01-09 15:07:44