MySQL查询两个字段一起在结果
问题描述:
所以,我有如下表:MySQL查询两个字段一起在结果
retailer register store
1 104 b
1 101 a
2 104 b
2 101 a
3 104 b
3 101 a
我找表中查询得到的结果,看起来像:
retailer register store
1 _ 101, 104 _ a, b
2 _ 101, 104 _ a, b
3 _ 101, 104 _ a, b
答
您可以使用GROUP_CONCAT和group by
select
retailer
, group_concat(register ORDER BY registed ASC SEPARATOR ', ')
, group_concat(store ORDER BY store ASC SEPARATOR ', ')
from my_table
group by retailer
,如果你需要的saparator你应该使用CONCAT
select
concat (retailer
, '_'
, group_concat(register ORDER BY registed ASC SEPARATOR ', ')
, '_'
, group_concat(store ORDER BY store ASC SEPARATOR ', '))
from my_table
group by retailer
答
您可以通过和GROUP_CONCAT与ORDER使用GROUP BY和分隔符
select retailer,
GROUP_CONCAT(register ORDER BY register DESC SEPARATOR ', ') AS register,
GROUP_CONCAT(store ORDER BY store DESC SEPARATOR ', ') AS store
from my_table
GROUP BY retailer
retailer register store
1 101, 104 a, b
2 101, 104 a, b
3 101, 104 a, b
是你的逗号旨在代表一个列分隔符预期的结果,或者是它的字面所有结果逗号的字符串分开吗? – Siyual