时间戳

首页 > 解决方案 > GROUP BY 影响 WHERE 子句

sql - GROUP BY 影响 WHERE 子句

问题描述

我有一个 SQL 查询，我想在其中查看没有与任何行关联的服务类型的声明。我下面的查询返回符合条件的一行。但是，还有另一行带有服务类型，这意味着我不想看到这个。由于我的查询组是查看行并返回它，然后分别查看第二行并将其删除

select distinct a.claim_id,
       count(a.receipt_id) as Count_Receipts, 
       sum(a.billed_amount) as Total_Billed_Amount,
       (case 
         when a.service_type_id is null then 
             count(a.receipt_id) 
         else 0 
       end) test
from cd_roc_claim_item a, 
     cd_hospital b , 
     cd_Claim_header c
where a.hospital_id = b.hospital_id
and a.claim_id=c.claim_id (+)
and b.hos_cat_id='PUBL'
and c.claim_status is null
and a.claim_id='123456'
group by a.claim_id, 
         a.service_type_id 
having count(a.receipt_id) =1
       and sum(a.billed_amount) in 
         ('80','160','240','320','400','480','560','640','720','800')
;

标签： sqloracle

解决方案

如果您只想报告service_type任何索赔项目中没有值的索赔：

select ci.claim_id
     , count(ci.receipt_id) as count_receipts
     , sum(ci.billed_amount) as total_billed_amount
     , count(*) as claim_items
from   cd_roc_claim_item ci
       left join cd_hospital ho
            on  ho.hospital_id = ci.hospital_id
       left join cd_claim_header cl
            on  cl.claim_id = ci.claim_id
where  cl.claim_status is null
group by ci.claim_id
    -- , ci.service_type_id  -- Edit (following comments): this was part of the problem
having count(ci.service_type_id) = 0
and sum(ci.billed_amount) in (80, 160, 240, 320, 400, 480, 560, 640, 720, 800);

CLAIM_ID   COUNT_RECEIPTS TOTAL_BILLED_AMOUNT CLAIM_ITEMS
---------- -------------- ------------------- -----------
1                       2                 160           2

问题在于医院类别，因为每个索赔项目都可能针对具有不同类别的不同医院。您是否只想查看所有项目都针对医院类别的索赔PUBL，或者至少有一个，或者什么？

测试设置：

create table cd_hospital
( hospital_id integer primary key
, hos_cat_id varchar2(10) );

create table cd_claim_header
( claim_id varchar2(10) primary key
, claim_status varchar2(10) );

create table cd_roc_claim_item
( claim_item_id integer generated always as identity primary key
, claim_id references cd_claim_header not null
, hospital_id references cd_hospital not null
, billed_amount number not null
, service_type_id integer
, receipt_id integer );

insert into cd_hospital (hospital_id, hos_cat_id) values (1, 'PUBL');
insert into cd_hospital (hospital_id, hos_cat_id) values (2, 'PRIV');

insert into cd_claim_header(claim_id, claim_status) values (1, null);
insert into cd_claim_header(claim_id, claim_status) values (2, 'CLOSED');

insert into cd_roc_claim_item (claim_id, hospital_id, billed_amount, service_type_id, receipt_id)
select ch.claim_id, ho.hospital_id, 80, null, 1
from   cd_claim_header ch
       cross join cd_hospital ho
where  ch.claim_id = 1
union all
select ch.claim_id, ho.hospital_id, 640, 123, 1
from   cd_claim_header ch
       cross join cd_hospital ho
where  ch.claim_id = 2;

根据评论进行编辑：

为了包含索赔中包含和不包含服务类型的项目的情况，我们将更新一行：

update cd_roc_claim_item set service_type_id = null
where  claim_id = 2 and hospital_id = 1;

现在测试数据如下所示：

select ci.claim_id, ch.claim_status, ci.hospital_id, ho.hos_cat_id, ci.service_type_id, ci.receipt_id
from   cd_roc_claim_item ci
       join cd_claim_header ch
            on  ch.claim_id = ci.claim_id
       join cd_hospital ho
            on  ho.hospital_id = ci.hospital_id
order by ch.claim_id;

CLAIM_ID   CLAIM_STATUS  HOSPITAL_ID HOS_CAT_ID  SERVICE_TYPE_ID  RECEIPT_ID
---------- ------------ ------------ ---------- ---------------- -----------
1                                  2 PRIV                                  1
1                                  1 PUBL                                  1
2          CLOSED                  2 PRIV                    123           1
2          CLOSED                  1 PUBL                                  1

据我了解，您想要报告索赔 1（汇总到一行），因为它的所有项目都没有服务类型。不应显示声明 2，因为其中一项具有服务类型。

推荐阅读