首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >postgresql -如何显示“in”操作符中的所有值?

postgresql -如何显示“in”操作符中的所有值?
EN

Stack Overflow用户
提问于 2015-01-29 15:24:12
回答 1查看 24关注 0票数 0

我的查询如下:

代码语言:javascript
复制
select lvl_desc, fct.patient_age_group as Age_group,fct.client_id,
fct.geog_algn_id,USC_3_CODE, PAYMENT_TYPE ,patient_gender_desc,
SUM(nvl(Tot_Mkt,0)) as Tot_Mkt
FROM MY_FACT_TABLE fct
     inner join MY_DIM_TABLE geo on fct.client_id = geo.client_id 
                           and fct.geog_algn_id = geo.geog_algn_id 
                           and fct.lvl_desc = geo.geog_lvl_15_desc
WHERE fct.client_id = 10000 
and fct.geog_algn_id = 10000 
and geog_lvl_03_desc = 'BC'
and USC_3_CODE = 31000
AND PAYMENT_TYPE = 'BLA2'
AND patient_gender_desc = 'MALE'
AND patient_age_group in ('19-25','3-5', '30-34', '35-39')
and lvl_desc = 'BLA1'
GROUP BY fct.client_id,fct.geog_algn_id,lvl_desc,USC_3_CODE, PAYMENT_TYPE ,patient_gender_desc, patient_age_group
order by lvl_desc,age_group;

我的查询返回年龄组30-34岁和35-39岁的数据,因为事实表有数据。我想返回失踪年龄组3-5和19-25的零(0)。我该怎么做?任何帮助都将不胜感激。谢谢。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2015-01-29 15:36:33

使用提供一些数据集,或者在需要生成数据范围时提供一些集-返回函数 (比如generate_series()) (并使用左联接,在没有数据的情况下获取NULL ):

代码语言:javascript
复制
select lvl_desc, fct.patient_age_group as Age_group,fct.client_id,
       fct.geog_algn_id,USC_3_CODE, PAYMENT_TYPE ,patient_gender_desc,
       SUM(nvl(Tot_Mkt,0)) as Tot_Mkt
FROM (VALUES ('19-25', '3-5', '30-34', '35-39')) v(patient_age_group)
left join MY_FACT_TABLE fct on fct.patient_age_group = v.patient_age_group
left join MY_DIM_TABLE geo on fct.client_id = geo.client_id 
                          and fct.geog_algn_id = geo.geog_algn_id 
                          and fct.lvl_desc = geo.geog_lvl_15_desc
WHERE fct IS NULL OR (
    fct.client_id = 10000 
    and fct.geog_algn_id = 10000 
    and geog_lvl_03_desc = 'BC'
    and USC_3_CODE = 31000
    AND PAYMENT_TYPE = 'BLA2'
    AND patient_gender_desc = 'MALE'
    and lvl_desc = 'BLA1'
)
GROUP BY fct.client_id,fct.geog_algn_id,lvl_desc,USC_3_CODE,
         PAYMENT_TYPE ,patient_gender_desc, patient_age_group
order by lvl_desc,age_group;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/28218262

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档