常用的一些特殊的sql整理

JSON_CONTAINS，一对多时关联字段为使用逗号拼接的id

多对多的关系中，我们也可以不是用中间表来存储关联信息，而将其简化为类似‘一对多’的效果。

比如：书和作者的对应关系中，一本书对应多个作者，一个作者对应多本书，正常建表的话，我们应该有三张表：书、作者、书和作者的中间表。我们也可以将作者的信息存储到书的表中，在书的表中新增一个字段来存储作者的信息，多个作者之间使用逗号隔开，如图。

使用JSON_CONTAINS进行表的关联

查询书及作者的信息,通过JSON_CONTAINS来进行关联

SELECT 
    *
FROM book b 
    LEFT JOIN author a ON JSON_CONTAINS(b.AUTHORS, concat(a.id))
ORDER BY b.id

+----+---------+---------+------+---------+
| id | name    | authors | id   | name    |
+----+---------+---------+------+---------+
|  1 | 书名1   | [1,2]   |    1 | 作者1   |
|  1 | 书名1   | [1,2]   |    2 | 作者2   |
|  2 | 书名2   | [1,3]   |    1 | 作者1   |
|  2 | 书名2   | [1,3]   |    3 | 作者3   |
+----+---------+---------+------+---------+

使用group_concat对JSON_CONTAINS的字段进行拼接

使用group_concat将name进行拼接

    SELECT 
        b.id,b.name,group_concat(DISTINCT a.name) as author_names
    FROM book b 
        LEFT JOIN author a ON JSON_CONTAINS(b.AUTHORS, concat(a.id))
    GROUP BY b.id
    ORDER BY b.id

    +----+---------+-----------------+
    | id | name    | author_names    |
    +----+---------+-----------------+
    |  1 | 书名1   | 作者1,作者2     |
    |  2 | 书名2   | 作者1,作者3     |
    +----+---------+-----------------+

使用concat、group_concat将数据拼接为json对象

# 正常拼接
SELECT b.id,
       b.NAME,
       concat(
               '[',
               group_concat(DISTINCT  json_object(
                                                  'id', a.id,
                                                  'name', a.name
                                      )
                   ),
               ']'
           ) AS author_names
FROM book b
         LEFT JOIN author a ON JSON_CONTAINS(b.AUTHORS, CONCAT(a.id))
GROUP BY b.id
ORDER BY b.id;


# 通过case when then 来处理无关联对象时的情况
SELECT b.id,
       b.NAME,
       concat(
               '[',
               group_concat(DISTINCT (CASE
                                          WHEN a.id
                                              THEN json_object(
                                                  'id', a.id,
                                                  'name', a.name
                                              )
                   END)),
               ']'
           ) AS author_names
FROM book b
         LEFT JOIN author a ON JSON_CONTAINS(b.AUTHORS, CONCAT(a.id))
GROUP BY b.id
ORDER BY b.id;

+----+---------+-------------------------------------------------------------+
| id | NAME    | author_names                                                |
+----+---------+-------------------------------------------------------------+
|  1 | 书名1   | [{"id": 1, "name": "作者1"},{"id": 2, "name": "作者2"}]     |
|  2 | 书名2   | [{"id": 1, "name": "作者1"},{"id": 3, "name": "作者3"}]     |
+----+---------+-------------------------------------------------------------+