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)