Use the GROUP_CONCAT function of MySQL to implement aggregate multiplication.
The MySQL aggregate function provides addition, average, minimum, and maximum, but not multiplication. Here we use the existing MYSQL GROUP_CONCAT function to implement aggregation multiplication.
Create table 'tb _ seq '('num' int (10) not null, 'seq _ type' enum ('yellow', 'green', 'red') not null) ENGINE = InnoDB default charset = utf8;
Insert into 'tb _ seq '('num', 'seq _ type') values (4, 'green'), (1, 'red'), (3, 'green'), (1, 'red'), (8, 'red'), (4, 'yellow'), (8, 'red'), (7, 'yellow'), (10, 'red'), (1, 'red'), (1, 'red'), (1, 'yellow'), (5, 'green'), (9, 'red'), (1, 'yellow'), (6, 'yellow ');
DELIMITER $ USE't _ girl $ drop function if exists 'func _ multiple '$ create definer = 'root' @ 'localhost' FUNCTION 'func _ multiple' (f_nums (VARCHAR (1000 )) returns double (10, 2) BEGIN -- Created by ytt 2014/10/21. DECLARE result DOUBLE () DEFAULT 1; DECLARE cnt, I INT DEFAULT 0; SET cnt = CHAR_LENGTH (f_nums)-CHAR_LENGTH (REPLACE (f_nums ,',','')) + 1; WHILE I <cnt DO -- get multiple result. SET result = result * REVERSE (SUBSTRING_INDEX (f_nums, ',', I + 1), ',', 1); SET I = I + 1; end while; SET result = ROUND (result, 2); RETURN result; END $ DELIMITER;
SELECT seq_type,func_multiple(GROUP_CONCAT(num ORDER BY num ASC SEPARATOR ',')) AS multiple_num FROM tb_seq WHERE 1 GROUP BY seq_type;+----------+--------------+| seq_type | multiple_num |+----------+--------------+| yellow | 168.00 || green | 60.00 || red | 5760.00 |+----------+--------------+3 rows in set (0.00 sec)