mysql concat 限制,MySQL group_concat限制分组中的行

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?

解决方案

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)