sql2.txt

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

select t1.category,t1.count1,t2.count2,t3.count3
from (
	select business.category,count(information.information_id) as count1
	from business left join information
	on business.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 business.category
) t1,
(	select business.category,count(information.information_id) as count2
	from business left join information
	on business.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 business.category
) t2,
(	select business.category,decode(sum(information.pay_count),null,0,sum(information.pay_count)) as count3
	from business left join information
	on business.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 business.category
) t3
where t1.category=t2.category
and t1.category=t3.category
order by category