问题描述
下一个示例是我的数据库.
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)
这篇关于MySQL group_concat限制分组中的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!