Sql-Server

INNER JOIN 上的 DISTINCT 查詢

  • August 19, 2016

我有兩張表,一張 ( ARTISTS) 列出了不同的藝術家、詳細資訊和傳記,但沒有圖片。我有另一張桌子 ( ARCHIVECATALOGUES),裡面全是拍賣結果,裡面有我需要的圖片。我想生成一個查詢,它將為我提供表中的所有詳細資訊ARTISTS,並為每個藝術家從另一個表中包含一張圖像(不管是哪一張)。

所以我需要一個查詢這INNER JOIN兩個表。下面的查詢從表中提取每個結果ARCHIVESCATALOGUE,即每個藝術家有多個結果。但我只需要每個藝術家一個結果。我DISTINCTartist.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;

連接看起來很奇怪,但是如果不知道表和它們的鍵是什麼樣子就很難分辨。

引用自:https://dba.stackexchange.com/questions/147306