MySQL group_concat限制分组中的行 [英] 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 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

结果: network 80,22,53,3128,443

但不是我要找的结果，结果就是这样.

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

选择ex: 从tb_port a，tb_dest b，tb_rule c中选择dest，group_concat(端口限制2)，其中a.id = c.id_port和b.id = c.id_dest按目的地进行分组

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

我想要的结果

network    80,22
network 53,3128
network 443

如何仅使用SQL来实现此结果?

how to achieve this result only with SQL?

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

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

推荐答案

MySQL并没有使这种查询变得容易，但是(公认的不是很漂亮)一种解决方案是使用变量为每行赋予每个序列号. dest并按行号整数除以2分组，得到每组两个数字；

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)

要测试的SQLfiddle .

这篇关于MySQL group_concat限制分组中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！