sql一对多查询问题

Question

user表

id	name	tagsid
1	张三	1,2,3
2	李四	1,3
3	王二	2

tags表

id	name
1	同学
2	同事
3	朋友

user表里的tagsid对应tags里的id，想要实现类似如下查询结果，求问sql语句该怎么写呢？

{
  [0] => {
    ["id"] => 2
    ["name"] => "李四"
    ["tags"] => {
        [0] => {
            ["id"] => 1
            ["name"] => "同学"
        }
        [1] => {
            ["id"] => 3
            ["name"] => "朋友"
        }
  }
  [1] => {
    ["id"] => 3
    ["name"] => "王二"
    ["tags"] => {
        [0] => {
            ["id"] => 2
            ["name"] => "同事"
        }
  }
}

Answer 1

SELECT
    C.id,
    C.name,
    C.tag,
    D.name
FROM
    (
        SELECT
            A.id,
            A.`NAME`,
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    A.tags,
                    ',',
                    B.help_topic_id + 1
                ),
                ',' ,- 1
            ) AS tag
        FROM
            user AS A
        INNER JOIN mysql.help_topic AS B ON B.help_topic_id < (
            length(A.tags) - length(REPLACE(A.tags, ',', '')) + 1
        )
    ) AS C
LEFT JOIN tags AS D ON C.tag = D.id

参考文章：https://blog.csdn.net/ccmmzzl...

---

只能实现这种了，sql不可能直接查出来嵌套结构的数据，得用代码实现了

Answer 2

典型的N+1问题，纯SQL做不来（在我自己的知识体系内），建议看看这篇文章，写N+1查询优化的
N+1问题优化