# 在恒定時間內查找表的最小值和最大值

SELECT MIN(column) FROM table WHERE ...
SELECT MAX(column) FROM table WHERE ...


## 最佳答案

DELIMITER $$CREATE TRIGGER ai_table1_each AFTER INSERT ON table1 FOR EACH ROW BEGIN UPDATE db_info i SET i.minimum = LEAST(i.minimum, NEW.col) ,i.maximum = GREATEST(i.maximum, NEW.col) ,i.min_count = (i.min_count * (new.col < i.minumum)) + (i.minimum = new.col) + (i.minimum < new.col) ,i.max_count = (i.max_count * (new.col > i.maximum)) + (i.maximum = new.col) + (new.col > i.maximum) WHERE i.tablename = 'table1'; END$$

CREATE TRIGGER ad_table1_each AFTER DELETE ON table1 FOR EACH ROW
BEGIN
DECLARE new_min_count INTEGER;
DECLARE new_max_count INTEGER;

UPDATE db_info i
SET i.min_count = i.min_count - (i.minimum = old.col)
,i.max_count = i.max_count - (i.maximum = old.col)
WHERE i.tablename = 'table1';
SELECT i.min_count INTO new_min_count, i.max_count INTO new_max_count
FROM db_info i
WHERE i.tablename = 'table1';

IF new_max_count = 0 THEN
UPDATE db_info i
CROSS JOIN (SELECT MAX(col) as new_max FROM table1) m
SET i.max_count = 1
,i.maximum = m.new_max;
END IF;
IF new_min_count = 0 THEN
UPDATE db_info i
CROSS JOIN (SELECT MIN(col) as new_min FROM table1) m
SET i.min_count = 1
,i.minimum = m.new_min;
END IF;
END 

DELIMITER ;