ExhiID。ExhID 關聯 ExhiID。查詢 有參展商記錄的展會,並按參展商數量降序排列。
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;
T 先按 ExhID 分組聚合,得到每個展會的參展商計數。INNER JOIN 只保留有匹配計數的展會(自動過濾無參展商記錄)。ORDER BY T.ExhCount DESC 實現按數量降序。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;
窗口函數避免派生表,代碼更簡潔,計劃通常更優。