gpt4 book ai didi

mysql - MySQL 中的 JSON,返回 1(共 3 个)部门,列出所有员工

转载 作者:行者123 更新时间:2023-12-04 15:27:33 26 4
gpt4 key购买 nike

在部门表中,我有两个字段:

documentid, which is INT
jsondocument which is JSON

我执行了以下查询:

INSERT INTO department VALUES
(1,'{"department":{
"deptid":"d1",
"deptname":"Marketing",
"deptroom":"Room 7",
"deptphone":["465-8541","465-8542","465-8543"],
"employee":[{
"empid":"e1",
"empname":"Mary Jones",
"empphone":"465-8544",
"empemail":["mjones@gmail.com","mjones@company.com"]},
{
"empid":"e2",
"empname":"Tom Robinson",
"empphone":"465-8545",
"empemail":["trobinson@gmail.com","trobinson@company.com"]},
{
"empid":"e3",
"empname":"Olivia Johnson",
"empphone":"465-8546",
"empemail":["ojohnson@gmail.com","ojohnson@company.com"]}
]}} ' );

使用相同的查询,我又添加了 2 个部门,每个部门有 3 名员工。 Demo on DB Fiddle .我只想返回 1 个部门并列出所有员工,因此它看起来像这样:

部门名称 |员工
“营销” | "Mary Jones, Tom Robinson, Olivia Johnson"(引号的位置无关紧要)

但我能弄清楚的最接近的是这个查询,它列出了所有部门,每个部门只列出了第一名员工:

select
jsondocument->'$.department.deptname' as deptname,
jsondocument->'$.department.employee[0].empname' as employees
from department;

这是家庭作业 - 初学者类(class),我已经努力学习,甚至达到了这一点。任何帮助将不胜感激。

最佳答案

这很简单,选择所有名称进行营销

select
jsondocument->'$.department.deptname' as deptname,
jsondocument->'$.department.employee[*].empname' as employees
from department
HAVING deptname = 'Marketing';
deptname    | employees                                       :---------- | :-----------------------------------------------"Marketing" | ["Mary Jones", "Tom Robinson", "Olivia Johnson"]

db<> fiddle here

关于mysql - MySQL 中的 JSON,返回 1(共 3 个)部门,列出所有员工,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/61979056/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com