--------------------这个sql查询没有问题---------------------------
select
bhosp_id,sum(bviola)
from (
select
ahosp_id,bhosp_id,chosp_id,aviola,bviola,cviola
from (
SELECT hosp_id ahosp_id,1 aviola
FROM TD_AUDIT_CASE_SUSP_VIOLA
WHERE AUDIT_STATUS = ‘08’) a
full join (
SELECT hosp_id bhosp_id,1 bviola
FROM TD_AUDIT_CASE_SUSP_VIOLA
WHERE AUDIT_STATUS = ‘02’
) b
on ahosp_id=bhosp_id
full join (
SELECT hosp_id chosp_id,1 cviola
FROM TD_AUDIT_CASE_SUSP_VIOLA
WHERE AUDIT_STATUS = ‘09’
) c
on bhosp_id=chosp_id)
group by bhosp_id
---------------------但是加上另一个字段就有问题了
select
bhosp_id,sum(bviola),sum(aviola)
from (
select
ahosp_id,bhosp_id,chosp_id,aviola,bviola,cviola
from (
SELECT hosp_id ahosp_id,1 aviola
FROM TD_AUDIT_CASE_SUSP_VIOLA
WHERE AUDIT_STATUS = ‘08’) a
full join (
SELECT hosp_id bhosp_id,1 bviola
FROM TD_AUDIT_CASE_SUSP_VIOLA
WHERE AUDIT_STATUS = ‘02’
) b
on ahosp_id=bhosp_id
full join (
SELECT hosp_id chosp_id,1 cviola
FROM TD_AUDIT_CASE_SUSP_VIOLA
WHERE AUDIT_STATUS = ‘09’
) c
on bhosp_id=chosp_id)
group by bhosp_id