表 test:id(PK), install, day(日期整數), aid(分組鍵)。需求:按 aid 分組,取每組 day 最大(最新)的那行完整記錄,再按 install 降序排列。
SELECT t1.*
FROM `test` AS t1
JOIN (
SELECT aid, MAX(day) AS max_day
FROM `test`
GROUP BY aid
) AS t2 ON t1.aid = t2.aid AND t1.day = t2.max_day
ORDER BY t1.install DESC;
t2 只有 aid, max_day 兩列,極小aid 索引(建議建立 INDEX(aid, day))day,會全部返回WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY aid ORDER BY day DESC, id DESC) AS rn
FROM `test`
)
SELECT * FROM ranked WHERE rn = 1 ORDER BY install DESC;
ROW_NUMBER() 保證每組僅取 1 行,配合 id DESC 做確定性去重DROP PROCEDURE IF EXISTS set_rank;
DELIMITER //
CREATE PROCEDURE set_rank()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE total INT;
SELECT COUNT(*) INTO total FROM suppliers_performance;
UPDATE suppliers_performance SET score_rank = 0;
WHILE i <= total DO
UPDATE suppliers_performance
SET score_rank = i
WHERE id IN (
SELECT id FROM (
SELECT id FROM suppliers_performance
WHERE score_rank = 0
ORDER BY score LIMIT 1
) AS t
);
SET i = i + 1;
END WHILE;
END//
DELIMITER ;
邏輯:循環將未排名記錄按 score 排序,每次取第一名賦予遞增排名,直到全部排名完成。
⚠️ 效能警告:此過程對大表極慢(O(N²)),僅供學習參考。生產環境請用窗口函數或應用層計算。
-- 方案一:支援衍生表 JOIN
CREATE INDEX idx_aid_day ON test(aid, day);
-- 方案二:支援窗口函數/覆蓋索引
CREATE INDEX idx_aid_day_install ON test(aid, day DESC, install DESC);
SELECT *, MAX(day) FROM test GROUP BY aid* 值不確定(MySQL 5.7+ 預設報錯),不可用。HAVING day = MAX(day)day 唯一對應最大值時正確,否則返回多行或錯行。