欢迎访问 生活随笔！
TAG聚合

生活随笔

当前位置： 首页 >

sql array 数组基本用法(四)

发布时间：2025/4/5 33 豆豆
生活随笔 收集整理的这篇文章主要介绍了 sql array 数组基本用法(四) 小编觉得挺不错的,现在分享给大家,帮大家做个参考.
查询嵌套数组
SELECT "800M" AS race,[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]AS participants
这是一行数据，查询的时候需要把数据展开
WITH races AS (SELECT "800M" AS race,[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]AS participants) SELECTrace,participant FROM races r CROSS JOIN UNNEST(r.participants) as participant; raceparticipant
800M{Rudisha, [23.4, 26.3, 26.4, 26.1]}
800M{Makhloufi, [24.5, 25.4, 26.6, 26.1]}
800M{Murphy, [23.9, 26, 27, 26]}
800M{Bosse, [23.6, 26.2, 26.5, 27.1]}
800M{Rotich, [24.7, 25.6, 26.9, 26.4]}
800M{Lewandowski, [25, 25.7, 26.3, 27.2]}
800M{Kipketer, [23.2, 26.1, 27.3, 29.4]}
800M{Berian, [23.7, 26.1, 27, 29.3]}

总结

以上是生活随笔为你收集整理的sql array 数组基本用法(四)的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。