francs=> SELECT base.nr, francs-> multiples.multiple francs-> FROM (SELECT generate_series(1,10) AS nr) base francs-> JOIN (SELECT generate_series(1,5) AS b_nr, base.nr * 2AS multiple) multiples francs-> ON multiples.b_nr = base.nr ; ERROR: invalid reference toFROM-clause entry for table "base" LINE 4: JOIN (SELECT generate_series(1,5) AS b_nr, base.nr * 2AS ... ^ HINT: There is an entry for table "base", but it cannot be referenced from this part of the query.
使用 LATERAL JOIN
1 2 3 4 5 6 7 8
SELECT base.nr, multiples.multiple FROM (SELECT generate_series(1,10) AS nr) base, LATERAL ( SELECT multiples.multiple FROM ( SELECT generate_series(1,5) AS b_nr, base.nr * 2 AS multiple ) multiples WHERE multiples.b_nr = base.nr ) multiples;
查询结果
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
francs=> SELECT base.nr, francs-> multiples.multiple francs-> FROM (SELECT generate_series(1,10) AS nr) base, francs-> LATERAL ( francs(> SELECT multiples.multiple FROM francs(> ( SELECT generate_series(1,5) AS b_nr, base.nr * 2 AS multiple ) multiples francs(> WHERE multiples.b_nr = base.nr francs(> ) multiples; nr | multiple ----+---------- 1 | 2 2 | 4 3 | 6 4 | 8 5 | 10 (5 rows)
francs=> CREATE FUNCTION multiply(INT, INT) francs-> RETURNS INT francs-> LANGUAGE SQL francs-> AS francs-> $$ francs$> SELECT $1 * $2; francs$> $$ francs-> ; CREATE FUNCTION francs=> SELECT base.nr, francs-> multiple francs-> FROM (SELECT generate_series(1,10) AS nr) base, francs-> multiply(base.nr, 2) AS multiple; nr | multiple ----+---------- 1 | 2 2 | 4 3 | 6 4 | 8 5 | 10 6 | 12 7 | 14 8 | 16 9 | 18 10 | 20 (10 rows)
9.2 版
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
[pg92@redhatB ~]$ psql francs francs psql (9.2.1) Type "help"for help. francs=> CREATE FUNCTION multiply(INT, INT) francs-> RETURNS INT francs-> LANGUAGE SQL francs-> AS francs-> $$ francs$> SELECT $1 * $2; francs$> $$; CREATE FUNCTION francs=> SELECT base.nr, francs-> multiple francs-> FROM (SELECT generate_series(1,10) AS nr) base, francs-> multiply(base.nr, 2) AS multiple; ERROR: function expression inFROM cannot refer to other relations of same query level LINE 4: multiply(base.nr, 2) AS multiple;
