萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 查詢重復關聯並按關聯數量排序:一對多場景實戰

場景建模

  • ExhibitionOnline(主表):主鍵 ExhiID。
  • Exhibitor(從表):外鍵 ExhID 關聯 ExhiID。
  • 關係:一對多(一個展會對應多個參展商)。

需求

查詢 有參展商記錄的展會,並按參展商數量降序排列。

標準寫法(派生表 + JOIN)

SELECT EO.*, T.ExhCount
FROM ExhibitionOnline EO
INNER JOIN (
    SELECT ExhID, COUNT(*) AS ExhCount
    FROM Exhibitor
    GROUP BY ExhID
) T ON EO.ExhiID = T.ExhID
ORDER BY T.ExhCount DESC;

解析

  1. 子查詢 T 先按 ExhID 分組聚合,得到每個展會的參展商計數。
  2. 主查詢 INNER JOIN 只保留有匹配計數的展會(自動過濾無參展商記錄)。
  3. ORDER BY T.ExhCount DESC 實現按數量降序。

擴展:窗口函數寫法(SQL Server 2012+)

SELECT DISTINCT
    EO.*,
    COUNT(Ex.ExhID) OVER (PARTITION BY EO.ExhiID) AS ExhCount
FROM ExhibitionOnline EO
JOIN Exhibitor Ex ON EO.ExhiID = Ex.ExhID
ORDER BY ExhCount DESC;

窗口函數避免派生表,代碼更簡潔,計劃通常更優。

copyright © 萬盛學電腦網 all rights reserved