sqlite 有没有一种方法可以从聚合SQL查询中获得嵌套表？

mitkmikd 于 2023-10-23 发布在 SQLite

关注(0)|答案(1)|浏览(156)

我有两张table：

post有一个id和一些数据字段
tags，它有一个post id（posts.id上的外键），一个key和一个value字段（没有id或索引，只有元组）
文章：

| 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获取一行，所以我在这个查询中按照Id和group_concat标记的数据以json方式对查询进行分组
（注意，我是在SQLite数据库上做这个实验的，||是字符串连接的操作符）

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表的东西，也许是不需要解析的东西（甚至是可查询的东西）。
我想在 tags 单元格中得到一个表，类似这样

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

这在某种程度上是可以实现的吗（即使使用不同的sql dbms）？

sqlite

来源：https://stackoverflow.com/questions/65027400/is-there-a-way-to-obtain-a-nested-table-from-an-aggregate-sql-query

1条答案

按热度按时间

1yjd4xko1#

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 |酒吧|Baz|
| null| null| null| 1 |Foo|酒吧|
| 2 |T2| B2| null| null| null|
或者：

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 |酒吧|Baz|
| | | 酒吧|| bar ||
| 2 |T2| B2||||
参见demo。

赞(0）回复(0）举报 2023-10-23

我来回答

sqlite 有没有一种方法可以从聚合SQL查询中获得嵌套表？

1条答案

相关问题

热门标签

最新问答