PostgreSQL并加入别名

发布于 2025-02-05 18:38:35 字数 1066 浏览 2 评论 0原文

我有一个查询，以非常简化的形式，看起来像这样：

SELECT aa, bb, xx
FROM (VALUES ('a1', 'b1'), ('a2', 'b2')) as T1(aa, bb)
LEFT JOIN (
    SELECT xx FROM (VALUES
    ('a1', 'y1'), ('x2', 'y2')) as T2(xx, yy)
) as T3 ON T1.aa = T3.xx ;

执行以生成此问题：

 aa | bb |   xx   
----+----+--------
 a1 | b1 | a1
 a2 | b2 | (NULL)
(2 rows)

在现实生活中，列XX是使用案例语句和Alias定义的：

SELECT
    ...,
    (CASE WHEN rgr.age_years < 18 THEN '< 18'
          WHEN rgr.age_years < 36 THEN '26-35'
          WHEN rgr.age_years < 46 THEN '36-45'
          WHEN rgr.age_years < 56 THEN '46-55'
          WHEN rgr.age_years < 130 THEN '> 55' END)
        as row_name,
    ...
FROM
    (VALUES ('< 18'), ('26-35'), ('36-45'),
            ('46-55'), ('> 55'))
    as prn(possible_row_name)
LEFT JOIN other_table rgr 
  ON prn.possible_row_name = ??row_name??

显然?? ROW_NAME ??是一个别名，因此在我指定加入时无法使用。但是我没有找到正确的配方。

关键是我正在对表rgr进行选择，并且我希望所有的年龄段都存在，即使没有实体满足该数字。

原文

I have a query that, in very simplified form, looks like this:

SELECT aa, bb, xx
FROM (VALUES ('a1', 'b1'), ('a2', 'b2')) as T1(aa, bb)
LEFT JOIN (
    SELECT xx FROM (VALUES
    ('a1', 'y1'), ('x2', 'y2')) as T2(xx, yy)
) as T3 ON T1.aa = T3.xx ;

which executes to produce this:

 aa | bb |   xx   
----+----+--------
 a1 | b1 | a1
 a2 | b2 | (NULL)
(2 rows)

In real life the column xx is defined using a case statement and alias:

SELECT
    ...,
    (CASE WHEN rgr.age_years < 18 THEN '< 18'
          WHEN rgr.age_years < 36 THEN '26-35'
          WHEN rgr.age_years < 46 THEN '36-45'
          WHEN rgr.age_years < 56 THEN '46-55'
          WHEN rgr.age_years < 130 THEN '> 55' END)
        as row_name,
    ...
FROM
    (VALUES ('< 18'), ('26-35'), ('36-45'),
            ('46-55'), ('> 55'))
    as prn(possible_row_name)
LEFT JOIN other_table rgr 
  ON prn.possible_row_name = ??row_name??

Obviously ??row_name?? is an alias and so is not available at the time I specify the join. But I've not found the right formulation.

The point is simply that I'm doing a select on table rgr and I want all the age tranches present, even if no entity satisfies that number.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

酷遇一生 2025-02-12 18:38:35

您可以在表Expression RGR中飞行创建列，如以下方式

SELECT
    ...,
    rgr.row_name,
    ...
FROM
    (VALUES ('< 18'), ('26-35'), ('36-45'),
            ('46-55'), ('> 55'))
    as prn(possible_row_name)
LEFT JOIN ( -- table expression rgr defined here
  select t.*,
     CASE WHEN rgr.age_years < 18 THEN '< 18'
          WHEN rgr.age_years < 36 THEN '26-35'
          WHEN rgr.age_years < 46 THEN '36-45'
          WHEN rgr.age_years < 56 THEN '46-55'
          WHEN rgr.age_years < 130 THEN '> 55' END
        as row_name
  from other_table t
) rgr ON prn.possible_row_name = rgr.row_name

You can create the column on the fly inside the table expression rgr, as in:

SELECT
    ...,
    rgr.row_name,
    ...
FROM
    (VALUES ('< 18'), ('26-35'), ('36-45'),
            ('46-55'), ('> 55'))
    as prn(possible_row_name)
LEFT JOIN ( -- table expression rgr defined here
  select t.*,
     CASE WHEN rgr.age_years < 18 THEN '< 18'
          WHEN rgr.age_years < 36 THEN '26-35'
          WHEN rgr.age_years < 46 THEN '36-45'
          WHEN rgr.age_years < 56 THEN '46-55'
          WHEN rgr.age_years < 130 THEN '> 55' END
        as row_name
  from other_table t
) rgr ON prn.possible_row_name = rgr.row_name

回复收藏 0 原文

~没有更多了~