SQL 查詢重復字段並按出現次數排序：JOIN + 子查詢聚合實現

場景

表 exhibitiononline (PK: exhiid) 與 exhibitor (FK: exhid → exhiid) 為一對多關系。需查詢 exhibitiononline 中在 exhibitor 有關聯記錄的所有行，並按 exhid 出現次數降序排列。

解決方案

SELECT eo.*
FROM exhibitiononline eo
JOIN (
    SELECT exhid, COUNT(*) AS cnt
    FROM exhibitor
    GROUP BY exhid
) e_cnt ON eo.exhiid = e_cnt.exhid
ORDER BY e_cnt.cnt DESC;

邏輯拆解

1. 子查詢：GROUP BY exhid 聚合每個展會的參展商數量 cnt。
2. 主查詢：JOIN 關聯，僅保留有參展商的展會（內連接天然過濾無匹配行）。
3. ORDER BY cnt DESC：參展商多的展會優先顯示。

等效替代寫法

-- EXISTS 相關子查詢 (不需輸出 cnt 列時)
SELECT * FROM exhibitiononline eo
WHERE EXISTS (SELECT 1 FROM exhibitor e WHERE e.exhid = eo.exhiid)
ORDER BY (SELECT COUNT(*) FROM exhibitor e WHERE e.exhid = eo.exhiid) DESC;

-- APPLY (SQL Server 專用，邏輯清晰)
SELECT eo.*
FROM exhibitiononline eo
CROSS APPLY (
    SELECT COUNT(*) AS cnt FROM exhibitor e WHERE e.exhid = eo.exhiid
) ca
ORDER BY ca.cnt DESC;

索引建議

• exhibitor：CREATE INDEX IX_Exhibitor_Exhid ON exhibitor(exhid); 加速分組聚合與 JOIN。
• exhibitiononline：PK exhiid 已為聚集索引，無需額外索引。