需求:依 flag_3 分組,按 createtime DESC 排序,取每組最新一筆。
WITH ranked AS (
SELECT
workh.corp_code, workh.corp_name, workh.begin_date, workh.createtime,
workh.flag_1, workh.flag_2,
workb.main_id, workb.production_quantity, workb.spare_capacity,
workb.equipment_code, workb.flag_3,
ROW_NUMBER() OVER (PARTITION BY workb.flag_3 ORDER BY workh.createtime DESC) AS rn
FROM imp_workorderh workh
RIGHT JOIN imp_workorderb workb ON workb.main_id = workh.id
WHERE workh.teachingplanid = '0000AA10000000016F6U'
)
SELECT *
FROM ranked
WHERE rn = 1;
PARTITION BY workb.flag_3:依旗標分組。ORDER BY workh.createtime DESC:組內按建立時間倒序,最新排第一。ROW_NUMBER():為每組產生 1,2,3... 序號。WHERE rn = 1 保留各組首筆。-- 相關子查詢 (效能較差)
SELECT * FROM imp_workorderb b
WHERE b.main_id = (
SELECT TOP 1 h.id FROM imp_workorderh h
WHERE h.id = b.main_id
ORDER BY h.createtime DESC
);
PARTITION BY 與 ORDER BY 欄位有合適索引 (如 INDEX(flag_3, createtime DESC))。ROW_NUMBER() 需排序,觀察 Sort 運算子是否溢寫 TempDB。MAX() + JOIN 或 APPLY (OUTER APPLY) 更輕量。