我有一个查询,在非常简化的形式,看起来像这样:
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
是使用case语句和别名定义的:
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
上做一个选择,我想要所有的年龄分级,即使没有实体满足这个数字。
可以在表表达式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