sql1.txt

来自「这个是完整的wap项目的源码 开发语言 Java 系统架构 Struts +」· 文本 代码 · 共 45 行

select t1.cp_id,company_name,t1.count1,t2.count2,t3.count3,t4.count4
from (
	select cp.cp_id,company_name,count(business.business_id) as count1
	from cp left join business
	on cp.cp_id=business.cp_id
	group by cp.cp_id,company_name
) t1,
(	select a.cp_id,count(information.information_id) as count2
	from (
		select cp.cp_id,business.business_id
		from cp left join business
		on cp.cp_id=business.cp_id
	) a left join information
	on a.business_id=information.business_id
	and information.reg_date between TO_DATE('1900-11-21','YYYY-MM-DD   HH24:MI:SS') 
	and TO_DATE('2900-11-21','YYYY-MM-DD   HH24:MI:SS')
	group by a.cp_id
) t2,
(	select a.cp_id,count(information.information_id) as count3
	from (
		select cp.cp_id,business.business_id
		from cp left join business
		on cp.cp_id=business.cp_id
	) a left join information
	on a.business_id=information.business_id
	and information.is_pub=1
	and information.reg_date between TO_DATE('1900-11-21','YYYY-MM-DD   HH24:MI:SS') 
	and TO_DATE('2900-11-21','YYYY-MM-DD   HH24:MI:SS')
	group by a.cp_id
) t3,
(	select a.cp_id,count(information.information_id) as count4
	from (
		select cp.cp_id,business.business_id
		from cp left join business
		on cp.cp_id=business.cp_id
	) a left join information
	on a.business_id=information.business_id
	and information.is_pub=0
	and information.reg_date between TO_DATE('1900-11-21','YYYY-MM-DD   HH24:MI:SS') 
	and TO_DATE('2900-11-21','YYYY-MM-DD   HH24:MI:SS')
	group by a.cp_id
) t4
where t1.cp_id=t2.cp_id
and t1.cp_id=t3.cp_id
and t1.cp_id=t4.cp_id