SQL检索多维数组 [英] SQL to retrieve multidimensional Array

问题描述

我正在尝试学习SQL，但是遇到了以下问题.我可以使用命令式编程轻松解决它，但想知道是否可以仅通过查询来解决此问题:

I'm trying to learn SQL but hitting a wall with the following problem. I can solve it easily using imperative programming but would like to know if it's possible to solve this problem using just a query:

输入

Table 1 (users)

ID  | Firstname
--------------
1   | Felix
2   | Michael
3   | Tobias

Table 2 (hobbies)

ID  | Hobby     | User
------------------------
1   | cooking   | 1
2   | cat       | 1
3   | piano     | 2

想要的输出

{
    "users": [{
            "firstname": "Felix",
            "hobbies": [{
                "id": 1,
                "name": "cooking"
            }, {
                "id": 2,
                "name": "cat"
            }]
        },
        {
            "firstname": "Michael",
            "hobbies": [{
                "id": 3,
                "name": "piano"
            }]
        },
        {
            "firstname": "Tobias",
            "hobbies": []
        }
    ]
}

当然不需要直接为JSON.对于联接，到目前为止，我为每个爱好x用户创建了一行(对于Felix，则创建了两行)

Doesn't need to be directly JSON of course. With joins I've come so far that either a row is created for each hobby x user (two rows for Felix)

Felix | cooking
Felix | cat
Michael | piano

或者某些信息丢失了(费利克斯的猫迷路了)

or that some information got lost (Felix' cat got lost)

Felix | cooking
Michael | piano

推荐答案

第二次更新

我不喜欢这样，感觉太像蛮力"了，我想还有一种更优雅的方式...

Second Update

I don't like this it feels too much like "brute force", and I guess there is a more elegant way ...

select concat('{ "users": [', group_concat(json.hobbies), '] }') as hobbies
from
(
    select concat('{"firstname": "',u.firstname,'", "hobbies": [', group_concat(json_object('id',h.id,'name',h.hobby),''), ']}') hobbies
    from users u
        left join hobbies h on u.id = h.user
    group by u.id
    order by u.firstname
) as json

输出

{ "users": [{"firstname": "Felix", "hobbies": [{"id": 1, "name": "cooking"},{"id": 2, "name": "cat"}]},{"firstname": "Michael", "hobbies": [{"id": 3, "name": "piano"}]},{"firstname": "Tobias", "hobbies": [{"id": null, "name": null}]}] }

---

首次更新

---

First Update

我已经失去了使用MySQL JSON的能力(目前).我能得到的最接近的是以下内容，我猜可能仍然有用:

I've lost my battle with MySQL JSON (for now). The closest I could get was the following, which I guess might still be of some use:

select u.firstname, group_concat(json_object('id',h.id,'name',h.hobby),'') hobbies
from users u
left join hobbies h on u.id = h.user
group by u.id
order by u.firstname

输出

+-----------+-------------------------------------------------------+
| firstname | hobbies                                               | 
+-----------+-------------------------------------------------------+
| Felix     | {"id": 1, "name": "cooking"},{"id": 2, "name": "cat"} |
| Michael   | {"id": 3, "name": "piano"}                            |
| Tobias    | {"id": null, "name": null}                            | 
+-----------+-------------------------------------------------------+    

---

原始答案

---

Original Answer

不确定JSON，对于SQL端而言是否足够?

Not sure about JSON, will this suffice for the SQL side?

select
    u.id,
    u.firstname,
    group_concat(h.hobby ORDER BY h.ID SEPARATOR ',') as hobbies
from
    my_users u
    LEFT JOIN hobbies h ON
    u.ID = h.user
group by
    u.id,
    u.firstname

输出

+----+-----------+-------------+
| id | firstname |   hobbies   |
+----+-----------+-------------+    
|  1 | Felix     | cooking,cat |
|  2 | Michael   | piano       |
|  3 | Tobias    | NULL        |
+----+-----------+-------------+ 

这篇关于SQL检索多维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！