如何在选择查询预言机中的一列中合并多行数据

Question

例如：- 我有 3 张桌子。

学生 ：

student_id | name | rollNo | class 
         1 |  a1  | 12     | 5
         2 |  b1  | 11     | 5

地址

：一个用户可以有多个地址

street  | district| country |student_id
  gali1 | nanit   | india | 1
  gali2 | nanital | india | 1

书籍：用户可以有多本书

       book   | book_id |student_id
      history | 111      | 1
      Science | 112    | 1

这是一个例子。我希望数据在输出中是这样的.如果我选择student_id 1。那么这将是结果

student_id | name | rollNo | class | addresslist   | booklist
          1 |  a1  | 12     |  5    | some sort of |  some sort of
                                    |  list which  |  list which
                                    |  contain both|  contain both
                                    |  the address |  the book detail
                                    |  of user     |  of user

我正在使用不支持 json 的 12.1 现在它在 12.2 中。

地址列表可以像这样，您可以根据需要创建列表，但它应该包含所有这些数据。

[{street:"gali1","distict":"nanital","country":"india","student_id":1},{"street":"gali2","distict":"nanital","country":"india","student_id":1}]

书单相同

提前谢谢.

Answer 1

像这样：

WITH json_addresses ( address, student_id ) AS (
  SELECT '[' ||
         LISTAGG(
           '{"street":"' || street || '",'
             || '"district":" || district || '",'
             || '"country":" || country|| '"}',
           ','
         ) WITHIN GROUP ( ORDER BY country, district, street )
         || ']',
         student_id
  FROM   address
  GROUP BY student_id
),
json_books ( book, student_id ) AS (
  SELECT '[' ||
         LISTAGG(
           '{"book_id":"' || book_id || '",'
             || '"book":" || book || '"}',
           ','
         ) WITHIN GROUP ( ORDER BY book, book_id )
         || ']',
         student_id
  FROM   book
  GROUP BY student_id
)
SELECT s.*, a.address, b.book
FROM   student s
       INNER JOIN json_addresses a
       ON ( s.student_id = a.student_id )
       INNER JOIN json_books b
       ON ( s.student_id = b.student_id );