SQL多个UNNEST在单个选择列表中 [英] SQL multiple UNNEST in single select list

问题描述

我正在实施查询系统。我实现了嵌套功能。
现在，用户正在询问有关在单个select语句中使用多个嵌套的问题。
我使用PostgreSQL作为准则，因为大多数用户在我们的查询系统之前使用它。

I was implementing a Query system. I implemented unnest function. Now user was asking about using multiple unnest in a single select statement. I was using PostgreSQL as kind of guideline since most users was using it before our query system.

PostgreSQL的行为很奇怪：

PostgreSQL has such strange behavior:

postgres=# select unnest(array[1,2]), unnest(array[1,2]);
 unnest | unnest
--------+--------
      1 |      1
      2 |      2
(2 rows)

postgres=# select unnest(array[1,2]), unnest(array[1,2,3]);
 unnest | unnest
--------+--------
      1 |      1
      2 |      2
      1 |      3
      2 |      1
      1 |      2
      2 |      3
(6 rows)

我的实现始终是笛卡尔积。
我想知道，这背后的正确逻辑是什么？ PostgreSQL是在做正确的事还是只是一个错误？在ANSI文档或PostgreSQL文档中找不到清晰的描述。

My implementation was always generate as Cartesian product. I'm wondering, what's the correct logic behind this? Is PostgreSQL doing right thing or just a bug? I didn't find clear description in ANSI document or PostgreSQL document.

推荐答案

这并非无关紧要，而是关于PostgreSQL非常奇怪地处理<$中的多个返回集合的函数c $ c> SELECT 列表。 SELECT 中的返回集合的函数不是ANSI SQL标准的一部分。

This isn't about unnest as such, but about PostgreSQL's very weird handling of multiple set-returning functions in the SELECT list. Set-returning functions in SELECT aren't part of the ANSI SQL standard.

您会发现行为更加明智与 LATERAL 查询相比，应优先于 FROM 中的set-returning函数使用：

You will find behaviour much saner with LATERAL queries, which should be preferred over using a a set-returning function in FROM as much as possible:

select a, b FROM unnest(array[1,2]) a, LATERAL unnest(array[1,2,3]) b;

例如

regress=> select a, b FROM unnest(array[1,2]) a, LATERAL unnest(array[1,2,3]) b;
 a | b 
---+---
 1 | 1
 1 | 2
 1 | 3
 2 | 1
 2 | 2
 2 | 3
(6 rows)

唯一一次我仍在使用多个set-returning函数 SELECT 是当我想对两个都返回相同行数的函数中的值进行配对时。在9.4中将不再需要该参数，它具有多个参数 unstest 并支持 WITH ORDINALITY 。

The only time I still use multiple set-returning functions in SELECT is when I want to pair up values from functions that both return the same number of rows. The need for that will go away in 9.4, with multi-argument unnest and with support for WITH ORDINALITY.

这篇关于SQL多个UNNEST在单个选择列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！