如何使用 group-concat mysql 创建 json 格式?
如何用group-concat mysql创建json格式?
How create json format with group-concat mysql?
(我使用 MySQL)
(I use MySQL)
示例 1:
表 1:
email | name | phone
-------------------------------------
my1@gmail.com | Ben | 6555333
my2@gmail.com | Tom | 2322452
my2@gmail.com | Dan | 8768768
my1@gmail.com | Joi | 3434356
像没有给我格式的语法代码:
like syntax code that not give me the format:
select email, group-concat(name,phone) as list from table1
group by email
我需要的输出:
email | list
------------------------------------------------
my1@gmail.com | {name:"Ben",phone:"6555333"},{name:"Joi",phone:"3434356"}
my2@gmail.com | {name:"Tom",phone:"2322452"},{name:"Dan",phone:"8768768"}
谢谢
推荐答案
试试这个查询 -
SELECT
email,
GROUP_CONCAT(CONCAT('{name:"', name, '", phone:"',phone,'"}')) list
FROM
table1
GROUP BY
email;
JSON 格式结果 -
JSON format result -
+---------------+-------------------------------------------------------------+
| email | list |
+---------------+-------------------------------------------------------------+
| my1@gmail.com | {name:"Ben", phone:"6555333"},{name:"Joi", phone:"3434356"} |
| my2@gmail.com | {name:"Tom", phone:"2322452"},{name:"Dan", phone:"8768768"} |
+---------------+-------------------------------------------------------------+
相关文章