--查询出药品通用名、生产厂家、批号完全一样,报告单位一样,有3份(含)
select t4.pkid,t4.reportno,t4.name,
t4.Attribute,t4.company,t4.haptime,
t4.proacctime
from adr_mreport t4 --报告主表
left join adr_dreportmed t5 on t4.pkid=t5.pkid --联合报告细表
left join adr_med t on t5.medno=t.medno --联合药品信息表
where t.comname||t5.provider||t5.lotno||t4.company in
(select t6.comname||t2.provider||t2.lotno||tt.company from adr_mreport tt left join adr_dreportmed t2 on tt.pkid=t2.pkid
left join adr_med t6 on t6.medno=t2.medno
group by t6.comname||t2.provider||t2.lotno||tt.company
having count(t6.comname||t2.provider||t2.lotno||tt.company)>=3);
select t4.pkid,t4.reportno,t4.name,
t4.Attribute,t4.company,t4.haptime,
t4.proacctime
from adr_mreport t4 --报告主表
left join adr_dreportmed t5 on t4.pkid=t5.pkid --联合报告细表
left join adr_med t on t5.medno=t.medno --联合药品信息表
where t.comname||t5.provider||t5.lotno||t4.company in
(select t6.comname||t2.provider||t2.lotno||tt.company from adr_mreport tt left join adr_dreportmed t2 on tt.pkid=t2.pkid
left join adr_med t6 on t6.medno=t2.medno
group by t6.comname||t2.provider||t2.lotno||tt.company
having count(t6.comname||t2.provider||t2.lotno||tt.company)>=3);
t4.reportno,
t4.name,
t4.Attribute,
t4.company,
t4.haptime,
t4.proacctime
from adr_mreport t4 --报告主表
left join adr_dreportmed t5 on t4.pkid=t5.pkid --联合报告细表
left join adr_med t on t5.medno=t.medno --联合药品信息表
where EXISTS (select sum(1)
from adr_mreport tt left join adr_dreportmed t2 on tt.pkid=t2.pkid
left join adr_med t6 on t6.medno=t2.medno
WHERE tt.company = t4.company
AND t2.lotno = t5.lotno
AND t2.provider = t5.provider
AND t6.comname = t.comname
group by t6.comname,t2.provider,t2.lotno,tt.company
having SUM(1)> =3);