Mysql group_concat 限制分组中的行数

Question

下一个例子是我的数据库。

tb_portid  port1   802   223   534   31285   443

tb_destid  dest1   network2   local

tb_ruleid  id_port id_dest1   1       12   2       13   3       14   4       15   5       1

选择: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 按目标分组

结果:网络 80,22,53,3128,443

但不是我要的结果，结果应该是这样。

选择例子: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 按目标分组

我想要的结果

network    80,22network 53,3128network 443

如何只用SQL实现这个结果？

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

Answer 1

MySQL 并没有使这种查询变得简单，但一个(诚然不是很漂亮)解决方案是使用变量为每一行指定一个序列号，然后按行号整数除以 2 进行分组得到每组两个数字；

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 .