CODEWITHSUNDEEP
mysql
Marcos Carraro
Marcos Carraro

Reputation: 136

Mysql group_concat limit rows in grouping

The next example is my database.

tb_port
id  port
1   80
2   22
3   53
4   3128
5   443
tb_dest
id  dest
1   network
2   local

tb_rule
id  id_port id_dest
1   1       1
2   2       1
3   3       1
4   4       1
5   5       1

Select: select dest,group_concat(port) from tb_port a, tb_dest b, tb_rule c where a.id=c.id_port and b.id=c.id_dest group by dest

Result: network 80,22,53,3128,443

but is not the result I'm looking for, the result would be this.

Select ex: select dest,group_concat(port limit 2) from tb_port a, tb_dest b, tb_rule c where a.id=c.id_port and b.id=c.id_dest group by dest

result I would like

network    80,22
network 53,3128
network 443

how to achieve this result only with SQL?

Sqlfiddle: http://sqlfiddle.com/#!2/d11807

Upvotes: 1

Views: 1764

Answers (2)

Mihai
Mihai

Reputation: 26784

Here is a stored proc,you just put in the delimiter when you call it

DELIMITER $$

DROP PROCEDURE IF EXISTS explode_table $$
CREATE PROCEDURE explode_table(bound VARCHAR(255))

  BEGIN

    DECLARE id TEXT;
    DECLARE value TEXT;
    DECLARE occurance INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE splitted_value TEXT;
    DECLARE done INT DEFAULT 0;
    DECLARE cur1 CURSOR FOR  
     select dest,group_concat(port) from tb_port a, tb_dest b, tb_rule c 
     where a.id=c.id_port and b.id=c.id_dest and  dest != '' group by dest;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    DROP TEMPORARY TABLE IF EXISTS table2;
    CREATE TEMPORARY TABLE table2(
    `id` VARCHAR(255),
    `value` VARCHAR(255) NOT NULL
    ) ENGINE=Memory;

    OPEN cur1;
      read_loop: LOOP
        FETCH cur1 INTO id, value;
        IF done THEN
          LEAVE read_loop;
        END IF;

        SET occurance = (SELECT LENGTH(CONCAT(value,bound))
                                 - LENGTH(REPLACE(CONCAT(value,bound), bound, ''))
                                 +1);
        SET i=2;
        WHILE i <= occurance DO
          SET splitted_value =
          SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(value,bound),bound,i),bound,-2) ;

          INSERT INTO table2 VALUES (id, splitted_value);
          SET i = i + 2;

        END WHILE;
      END LOOP;

      SELECT * FROM table2;
    CLOSE cur1;
  END; $$



 CALL explode_table(',')

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180977

MySQL doesn't make this kind of query easy, but one (admittedly not very pretty) solution is to use a variable to give each row a sequence number per dest and just group by the row number integer divided by 2 to get two numbers in each group;

SELECT dest, GROUP_CONCAT(port ORDER BY rank) ports
FROM (
  SELECT dest, port, ( 
      CASE dest WHEN @curDest 
                THEN @curRow := @curRow + 1 
                ELSE @curRow := 1 AND @curDest := dest END) rank
  FROM tb_port a
  JOIN tb_rule c ON a.id = c.id_port
  JOIN tb_dest b ON b.id = c.id_dest, 
    (SELECT @curRow := 0, @curDest := '') r
  ORDER BY dest
) z
GROUP BY FLOOR(rank/2),dest
ORDER BY dest, MIN(rank)

An SQLfiddle to test with.

Upvotes: 2

Related Questions