"Original" using MySQL's group_concat function for aggregation multiplication

The MySQL aggregate function provides a plus, an average, a minimum, a maximum, etc., but does not provide multiplication, and here we use MySQL's existing group_concat function to achieve aggregate multiplication.

Create a sample table first:

CREATE TABLE ' tb_seq ' (' num ' int () not null, ' Seq_type ' enum (' Yellow ', ' green ', ' red ') is not null) Engine=innodb DEFAULT Charset=utf8;

Insert Sample data:

Insert INTO ' tb_seq ' (' num ', ' seq_type ') VALUES (4, ' green '), (1, ' Red '), (3, ' green '), (1, ' Red '), (8, ' Red '), (4, ' yellow '), (8, ' Red '), (7, ' Yellow '), (Ten, ' Red '), (1, ' Red '), (1, ' Red '), (1, ' Yellow '), (5, ' Red '), (9, ' Yellow '), (1, ' Yell Ow ');

The

Creates a string multiplication based on a comma delimiter, provided that the string is delimited by a number.

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 ( 10,2)  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 (REVERSE (Substring_index (f_nums, ', ', i+1)), ', ', 1));        set i = i + 1;       end while;      set result = round ( result,2);       return result;       end $ $DELIMITER  ;

Well, we use the functions I've created and the Group_concat aggregation functions from MySQL to make it easy to multiply.

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)

This article is from "God, we don't see!" "Blog, be sure to keep this provenance http://yueliangdao0608.blog.51cto.com/397025/1566281

"Original" using MySQL's group_concat function for aggregation multiplication