GROUP BY影响WHERE子句
GROUP BY影响WHERE子句
提问于 2018-05-28 20:02:43
我有一个SQL查询，其中我希望查看没有与任何行关联的服务类型的索赔。我下面的查询返回满足条件的一行。但是，还有另一行包含服务类型，这意味着我不想看到这种情况。因为group by查询看到行并返回它，然后单独查看第二行并将其删除
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')
;回答 1
Stack Overflow用户
回答已采纳
发布于 2018-05-28 22:08:32
如果您只想报告任何索赔项中没有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据我所知，您希望报告Claim1(聚合到一行)，因为它的任何项都没有服务类型。权利要求2不应显示，因为其中一项具有服务类型。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50566003
复制相关文章
相似问题
腾讯云开发者
