时间戳

首页 > 解决方案 > 有没有办法从聚合 sql 查询中获取嵌套表？

sql - 有没有办法从聚合 sql 查询中获取嵌套表？

问题描述

我有两张桌子：

帖子

| id | title | body |
+----+-------+------+
|  1 | t1    | b1   | 
+----+-------+------+
|  2 | t2    | b2   |
+----+-------+------+

标签

| post | key | value |
+------+-----+-------+
|  1   | bar | baz   |
+------+-----+-------+
|  1   | foo | bar   |
+------+-----+-------+

如果我想检索一些带有自己标签的帖子，可以使用类似的查询

select posts.*, tags.*
from posts
left join tags
on posts.id = tags.post

我会得到

| id | title | body | post | key | value |
+----+-------+------+------+-----+-------+
| 1  |    t1 |   b1 |    1 | bar |   baz |
+----+-------+------+------+-----+-------+
| 1  |    t1 |   b1 |    1 | foo |   bar |
+----+-------+------+------+-----+-------+
| 2  |    t2 |   b2 |      |     |       |
+----+-------+------+------+-----+-------+

但是我想为 post 获得单行，所以我用这个查询以 json 方式按 Id 和 group_concat 对标签的数据进行分组（请注意，我正在 SQLite db 上进行这个实验，|| 是字符串连接运算符）

select posts.*, "{" || group_concat('"' || tags.key || '": "' || tags.value || '"') || "}" as tags
from posts
left join tags
on posts.id = tags.post
group by posts.id

这就是结果

| id | title | body |                        tags |
+----+-------+------+-----------------------------+
| 1  |    t1 |   b1 | {"bar": "baz","foo": "bar"} |
+----+-------+------+-----------------------------+
| 2  |    t2 |   b2 |                             |
+----+-------+------+-----------------------------+

我的问题是：有没有办法以更好的方式聚合标签结果？我的意思是更类似于普通 sql 表的东西，也许是不需要解析的东西（甚至是可查询的东西）。

我想在标签单元格中得到一个表格，像这样

| id | title | body |        tags |
|    |       |      | key | value |
+----+-------+------+-----+-------+
| 1  |    t1 |   b1 | bar |   baz |
|    |       |      +-----+-------+
|    |       |      | foo |   bar |
+----+-------+------+-----+-------+
| 2  |    t2 |   b2 |     |       |
+----+-------+------+-----+-------+

这是否可以通过某种方式实现（即使使用不同的 sql dbms）？

标签： sqlsqlite

解决方案

SQL 查询的结果是由行和列组成的数据集，没有表中表或单元格中的单元格的概念。
最接近您的要求的是在null每组的第一行之后返回 s 或空白的查询：

SELECT CASE WHEN rn = 1 THEN t.id END id,
       CASE WHEN rn = 1 THEN t.title END title,
       CASE WHEN rn = 1 THEN t.body END body,
       t.post, t.key, t.value
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY t.key) rn
  FROM posts p LEFT JOIN tags t
  ON t.post = p.id 
) t
ORDER BY t.id, t.rn

结果：

ID 标题 身体 邮政 钥匙 价值
1 t1 b1 1 酒吧 巴兹
无效的 无效的 无效的 1 富 酒吧
2 t2 b2 无效的 无效的 无效的

或者：

SELECT CASE WHEN rn = 1 THEN t.id ELSE '' END id,
       CASE WHEN rn = 1 THEN t.title ELSE '' END title,
       CASE WHEN rn = 1 THEN t.body ELSE '' END body,
       COALESCE(t.post, '') post, 
       COALESCE(t.key,'') key,
       COALESCE(t.value, '') value
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY t.key) rn
  FROM posts p LEFT JOIN tags t
  ON t.post = p.id 
) t
ORDER BY t.id, t.rn

结果：

ID 标题 身体 邮政 钥匙 价值
1 t1 b1 1 酒吧 巴兹
1 富 酒吧
2 t2 b2

请参阅演示。

推荐阅读