Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- * 1. select last 10 record and sort.
- * 2. calc (`rowsA` + `rowsB`) / `rowsC`.
- * 3. use PROCEDURE and Variable LIMIT.
- */
- DROP PROCEDURE IF EXISTS last_10_sort;
- DELIMITER //
- CREATE PROCEDURE last_10_sort()
- BEGIN
- DECLARE record_total, record_from INT DEFAULT 0;
- SET @record_total = (SELECT COUNT(record_time) FROM tableA);
- SET @record_from = (@record_total - 10);
- IF @record_total < 10 THEN
- SELECT `record_time`, round(((`rowsA` + `rowsB`) / (SELECT rowsC FROM tableB WHERE bid = '1') * 100), 2) AS 'tmp' FROM tableA ORDER BY `record_time` LIMIT 10;
- ELSE
- PREPARE stmt_last_10_sort FROM
- "select `record_time`, round(((`rowsA` + `rowsB`) / (select rowsC from tableB where bid = '1') * 100), 2) as 'tmp' from tableA order by `record_time` limit ?, ?";
- EXECUTE stmt_last_10_sort USING @record_from, @record_total;
- DEALLOCATE PREPARE stmt_last_10_sort;
- END IF;
- END //
- DELIMITER ;
- CALL last_10_sort();
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement