最近呢、在做一个机电管理中的机电报修报表、用sql将数据查询出来、不用任何的报表插件!
这张是需求Table:我需要按照这种方式来做!
首先呢、我先将数据查询出来这个是我的查询语句:select osys.name osysName ,oar.name oarName,otl.name otlName ,odr.name odrName,count(pr.id)
from oa_property_repair pr , oa_system osys ,oa_road oar , oa_trouble_level otl, oa_deal_result odr
where pr.system_type = osys.code and pr.roadname = oar.code
and pr.trouble_level = otl.code and pr.deal_result = odr.code
GROUP BY osys.name,oar.name,otl.name,odr.name但是这样呢、效果不是很明显:我想只要路段分组、把路段先独立出来在慢慢处理上面所属系统的问题、还有就是所属系统只有三个、我也可以直接写死!
所谓的路段分组就是要这种效果:select oar.name oarName,count(pr.id)
from oa_property_repair pr ,oa_road oar
where pr.roadname = oar.code
GROUP BY oar.name这条语句出来的是:这样在加上所属系统、一级二级什么的!但是我写不出来啊这语句!o(︶︿︶)o 唉!
希望各位、有思路的给思路、有代码的直接上代码啊! 感激不尽!!!
这张是需求Table:我需要按照这种方式来做!
首先呢、我先将数据查询出来这个是我的查询语句:select osys.name osysName ,oar.name oarName,otl.name otlName ,odr.name odrName,count(pr.id)
from oa_property_repair pr , oa_system osys ,oa_road oar , oa_trouble_level otl, oa_deal_result odr
where pr.system_type = osys.code and pr.roadname = oar.code
and pr.trouble_level = otl.code and pr.deal_result = odr.code
GROUP BY osys.name,oar.name,otl.name,odr.name但是这样呢、效果不是很明显:我想只要路段分组、把路段先独立出来在慢慢处理上面所属系统的问题、还有就是所属系统只有三个、我也可以直接写死!
所谓的路段分组就是要这种效果:select oar.name oarName,count(pr.id)
from oa_property_repair pr ,oa_road oar
where pr.roadname = oar.code
GROUP BY oar.name这条语句出来的是:这样在加上所属系统、一级二级什么的!但是我写不出来啊这语句!o(︶︿︶)o 唉!
希望各位、有思路的给思路、有代码的直接上代码啊! 感激不尽!!!
select oar.name oarName,count(pr.id)
from oa_property_repair pr ,oa_road oar
where pr.roadname = oar.code
GROUP BY oar.name
查询出来的结果
与
原来的表作表连接,以路名为条件
哇塞~~~~这个想法好!!!
至于导出Excel、这个我是要直接显示到HTML上的!!!
如果是RDLC的话那你只要把数据放到一个DATATBLE里面。然后赋值给报表。
报表里面有一个自动增加列的。你直接设置就可以了。
如果你不明白。具体还要看下报表的做法。
这个容易实现。
呵呵~~~四楼是群里面的群友~~~我们商讨过了、不能解决~~~!!!
现在因为急用的问题!我只能占时用雷死人的子查询来解决这个问题了!select road.name,
(select count(0) from oa_property_repair t1 where t1.system_type='01' and t1.trouble_level='01' and t1.deal_result='000001' and t1.roadname=road.code and 1=1) a1,
(select count(0) from oa_property_repair t1 where t1.system_type='01' and t1.trouble_level='01' and t1.deal_result='000002' and t1.roadname=road.code and 1=1) a2,
(select count(0) from oa_property_repair t1 where t1.system_type='01' and t1.trouble_level='01' and t1.deal_result='000003' and t1.roadname=road.code and 1=1) a3,
(select count(0) from oa_property_repair t1 where t1.system_type='01' and t1.trouble_level='02' and t1.deal_result='000001' and t1.roadname=road.code and 1=1) a4,
(select count(0) from oa_property_repair t1 where t1.system_type='01' and t1.trouble_level='02' and t1.deal_result='000002' and t1.roadname=road.code and 1=1) a5,
(select count(0) from oa_property_repair t1 where t1.system_type='01' and t1.trouble_level='02' and t1.deal_result='000003' and t1.roadname=road.code and 1=1) a6,
(select count(0) from oa_property_repair t1 where t1.system_type='02' and t1.trouble_level='01' and t1.deal_result='000001' and t1.roadname=road.code and 1=1) a7,
(select count(0) from oa_property_repair t1 where t1.system_type='02' and t1.trouble_level='01' and t1.deal_result='000002' and t1.roadname=road.code and 1=1) a8,
(select count(0) from oa_property_repair t1 where t1.system_type='02' and t1.trouble_level='01' and t1.deal_result='000003' and t1.roadname=road.code and 1=1) a9,
(select count(0) from oa_property_repair t1 where t1.system_type='02' and t1.trouble_level='02' and t1.deal_result='000001' and t1.roadname=road.code and 1=1) a10,
(select count(0) from oa_property_repair t1 where t1.system_type='02' and t1.trouble_level='02' and t1.deal_result='000002' and t1.roadname=road.code and 1=1) a11,
(select count(0) from oa_property_repair t1 where t1.system_type='02' and t1.trouble_level='02' and t1.deal_result='000003' and t1.roadname=road.code and 1=1) a12,
(select count(0) from oa_property_repair t1 where t1.system_type='03' and t1.trouble_level='01' and t1.deal_result='000001' and t1.roadname=road.code and 1=1) a13,
(select count(0) from oa_property_repair t1 where t1.system_type='03' and t1.trouble_level='01' and t1.deal_result='000002' and t1.roadname=road.code and 1=1) a14,
(select count(0) from oa_property_repair t1 where t1.system_type='03' and t1.trouble_level='01' and t1.deal_result='000003' and t1.roadname=road.code and 1=1) a15,
(select count(0) from oa_property_repair t1 where t1.system_type='03' and t1.trouble_level='02' and t1.deal_result='000001' and t1.roadname=road.code and 1=1) a16,
(select count(0) from oa_property_repair t1 where t1.system_type='03' and t1.trouble_level='02' and t1.deal_result='000002' and t1.roadname=road.code and 1=1) a17,
(select count(0) from oa_property_repair t1 where t1.system_type='03' and t1.trouble_level='02' and t1.deal_result='000003' and t1.roadname=road.code and 1=1) a18
from oa_road road
突然发现原来自己不怎么的!!!
群号能公布一下么?
对不起、这个不是Oracle的群、是java交流的!
100756746
这个是群号!