我有两个表,我需要从中选择数据
用户
id email
----------
1 email1
2 email2
设定
usrid name value
--------------------
1 set1 value1
1 set2 value2
2 set1 value1
2 set2 value2
我使用的SQL是
SELECT u.email, s . *
FROM `sers` u
INNER JOIN `settings` s ON u.id = s.usrid
LIMIT 0 , 30
但是我明白了
email name value
------------------
email1 set1 value1
email1 set1 value1
email2 set2 value2
email2 set2 value1
有什么办法
email1 set1 value1 set1 value1 etc
email2 set2 value2 set2 value2 etc
从group_concat我尝试了这个
SELECT u.email, GROUP_CONCAT(s.name,s.value) as vals FROM `users` u INNER JOIN `settings` s ON u.id = s.usrid GROUP BY u.email
这给了
email1 set1value1,set1value1 etc
如何在逗号之间的每个值之间放置分隔符
喜欢
email1 set1=value1,set1=value1 etc
试试这个小提琴:
http://sqlfiddle.com/#!2/782e3/6
SELECT u.email, CONCAT_WS(',', GROUP_CONCAT( s.name),
GROUP_CONCAT( s.value)) AS allValues
FROM `user` u
INNER JOIN `settings` s ON u.id = s.usrid GROUP BY email
LIMIT 0 , 30
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句