Sql-Server
INNER JOIN 上的 DISTINCT 查詢
我有兩張表,一張 (
ARTISTS
) 列出了不同的藝術家、詳細資訊和傳記,但沒有圖片。我有另一張桌子 (ARCHIVECATALOGUES
),裡面全是拍賣結果,裡面有我需要的圖片。我想生成一個查詢,它將為我提供表中的所有詳細資訊ARTISTS
,並為每個藝術家從另一個表中包含一張圖像(不管是哪一張)。所以我需要一個查詢這
INNER JOIN
兩個表。下面的查詢從表中提取每個結果ARCHIVESCATALOGUE
,即每個藝術家有多個結果。但我只需要每個藝術家一個結果。我DISTINCT
在artist.ArtistID
球場上用過,但沒有用。這是程式碼:SELECT DISTINCT artist.ArtistID ,ArchiveCatalogues.IMAGER ,ArchiveCatalogues.AUCTION ,artist.surname ,artist.firstnames ,artist.dates ,artist.honorific ,artist.biog FROM artist INNER JOIN ArchiveCatalogues ON (ArchiveCatalogues.ARTIST = artist.surname) AND (ArchiveCatalogues.FIRSTNAME = artist.firstnames) WHERE artist.surname >= H * AND artist.surname < I GROUP BY artist.surname ,artist.firstnames ,artist.dates ,artist.honorific ,artist.biog ,ArchiveCatalogues.AUCTION ,artist.ArtistID ,ArchiveCatalogues.IMAGER
也許我應該使用
OUTER JOIN
?
首先(常見的誤解), distinct 不適用於單個列,您得到的是不同的行。這與您的完全相同
GROUP BY
,因此 distinct 是多餘的。其次,您需要確定您感興趣的重複項中的哪一行(或隨機獲取一個)。您可以使用視窗函式通過列舉每個藝術家的 ArchiveCatalogues 來實現此目的:
SELECT artist.ArtistID ,ArchiveCatalogues.IMAGER ,ArchiveCatalogues.AUCTION ,artist.surname ,artist.firstnames ,artist.dates ,artist.honorific ,artist.biog , row_number() over (partition by artist.ArtistID -- add wanted order her as: -- order by ... ) as rn FROM artist JOIN ArchiveCatalogues ON ArchiveCatalogues.ARTIST = artist.surname AND ArchiveCatalogues.FIRSTNAME = artist.firstnames WHERE artist.surname >= H * AND artist.surname < I
請注意,我刪除了該組,因為我不確定目的是什麼。現在您可以從那裡選擇第一行:
SELECT ArtistID , IMAGER , AUCTION , surname , firstnames , dates , honorific , biog FROM ( SELECT artist.ArtistID ,ArchiveCatalogues.IMAGER ,ArchiveCatalogues.AUCTION ,artist.surname ,artist.firstnames ,artist.dates ,artist.honorific ,artist.biog , row_number() over (partition by artist.ArtistID -- add wanted order her as: -- order by ... ) as rn FROM artist JOIN ArchiveCatalogues ON ArchiveCatalogues.ARTIST = artist.surname AND ArchiveCatalogues.FIRSTNAME = artist.firstnames WHERE artist.surname >= H * AND artist.surname < I ) as T WHERE rn = 1;
連接看起來很奇怪,但是如果不知道表和它們的鍵是什麼樣子就很難分辨。