6,108
社区成员
发帖
与我相关
我的任务
分享报表数据集字符数超过2000了，怎么办？
语句：
select a.* ,b.count2 问题数量,c.count3 设备问题,d.count4 人员问题, e.count5 管理问题,nvl(f.count6,0) 重大问题,g.count7 严重问题,l.count8 一般问题, i.count9 监督站及以上,j.count10 厂级,nvl(k.count11,0) 作业区自查自改 from (select t1.departmentname,t2.zgjd,count(*) as count1 from formtable_main_19 t2,hrmdepartment t1 where zgjd=0 and t2.bjdw=t1.id group by departmentname,zgjd ) a left join(select t1.departmentname,count(*) as count2 from formtable_main_19,hrmdepartment t1 where bjdw=t1.id group by departmentname ) b on a.departmentname=b.departmentname left join(select t1.departmentname,t2.wtlb,count(*) as count3 from formtable_main_19 t2,hrmdepartment t1 where wtlb=0 and t2.bjdw=t1.id group by departmentname,wtlb) c on a.departmentname=c.departmentname left join(select t1.departmentname,t2.wtlb,count(*) as count4 from formtable_main_19 t2,hrmdepartment t1 where wtlb=1 and t2.bjdw=t1.id group by departmentname,wtlb) d on a.departmentname=d.departmentname left join(select t1.departmentname,t2.wtlb,count(*) as count5 from formtable_main_19 t2,hrmdepartment t1 where wtlb=2 and t2.bjdw=t1.id group by departmentname,wtlb) e on a.departmentname=e.departmentname left join(select t1.departmentname,t2.yzcd,count(*) as count6 from formtable_main_19 t2,hrmdepartment t1 where yzcd=0 and t2.bjdw=t1.id group by departmentname,yzcd) f on a.departmentname=f.departmentname left join(select t1.departmentname,t2.yzcd,count(*) as count7 from formtable_main_19 t2,hrmdepartment t1 where yzcd=1 and t2.bjdw=t1.id group by departmentname,yzcd) g on a.departmentname=g.departmentname left join(select t1.departmentname,t2.yzcd,count(*) as count8 from formtable_main_19 t2,hrmdepartment t1 where yzcd=2 and t2.bjdw=t1.id group by departmentname,yzcd) l on a.departmentname=l.departmentname left join(select t1.departmentname,t2.wentxz,count(*) as count9 from formtable_main_19 t2,hrmdepartment t1 where wentxz=0 and t2.bjdw=t1.id group by departmentname,wentxz) i on a.departmentname=i.departmentname left join(select t1.departmentname,t2.wentxz,count(*) as count10 from formtable_main_19 t2,hrmdepartment t1 where wentxz=1 and t2.bjdw=t1.id group by departmentname,wentxz) j on a.departmentname=j.departmentname left join(select t1.departmentname,t2.wentxz,count(*) as count11 from formtable_main_19 t2,hrmdepartment t1 where wentxz=2 and t2.bjdw=t1.id group by departmentname,wentxz) k on a.departmentname=k.departmentname
...全文
请发表友善的回复…
发表回复
ORAClE SE 2016-01-15
- 打赏
- 举报
SELECT T1.DEPARTMENTNAME,
'0',
COUNT(DECODE(T2.ZGJD, '0', '1', NULL)) COUNT1,
COUNT(1) 问题数量,
COUNT(DECODE(T2.WTLB, '0', '1', NULL)) 设备问题,
COUNT(DECODE(T2.WTLB, '0', '1', NULL)) 人员问题,
COUNT(DECODE(T2.WTLB, '2', '1', NULL)) 管理问题,
NVL(COUNT(DECODE(T2.YZCD, '0', '1', NULL)), 0) 重大问题,
COUNT(DECODE(T2.YZCD, '1', '1', NULL)) 严重问题,
COUNT(DECODE(T2.YZCD, '2', '1', NULL)) 一般问题,
COUNT(DECODE(T2.WENTXZ, '0', '1', NULL)) 监督站及以上,
COUNT(DECODE(T2.WENTXZ, '1', '1', NULL)) 厂级,
NVL(COUNT(DECODE(T2.WENTXZ, '2', '1', NULL)), 0) 作业区自查自改
FROM FORMTABLE_MAIN_19 T2, HRMDEPARTMENT T1
WHERE T2.BJDW = T1.ID
GROUP BY DEPARTMENTNAME
lord_is_layuping 2014-11-24
- 打赏
- 举报
修改SQL！！！left join用简写吧，好长啊
select a.*,
b.count2 问题数量,
c.count3 设备问题,
d.count4 人员问题,
e.count5 管理问题,
nvl(f.count6, 0) 重大问题,
g.count7 严重问题,
l.count8 一般问题,
i.count9 监督站及以上,
j.count10 厂级,
nvl(k.count11, 0) 作业区自查自改
from (select t1.departmentname, t2.zgjd, count(*) as count1
from formtable_main_19 t2, hrmdepartment t1
where zgjd = 0
and t2.bjdw = t1.id
group by departmentname, zgjd) a
left join (select t1.departmentname, count(*) as count2
from formtable_main_19, hrmdepartment t1
where bjdw = t1.id
group by departmentname) b
on a.departmentname = b.departmentname
left join (select t1.departmentname, t2.wtlb, count(*) as count3
from formtable_main_19 t2, hrmdepartment t1
where wtlb = 0
and t2.bjdw = t1.id
group by departmentname, wtlb) c
on a.departmentname = c.departmentname
left join (select t1.departmentname, t2.wtlb, count(*) as count4
from formtable_main_19 t2, hrmdepartment t1
where wtlb = 1
and t2.bjdw = t1.id
group by departmentname, wtlb) d
on a.departmentname = d.departmentname
left join (select t1.departmentname, t2.wtlb, count(*) as count5
from formtable_main_19 t2, hrmdepartment t1
where wtlb = 2
and t2.bjdw = t1.id
group by departmentname, wtlb) e
on a.departmentname = e.departmentname
left join (select t1.departmentname, t2.yzcd, count(*) as count6
from formtable_main_19 t2, hrmdepartment t1
where yzcd = 0
and t2.bjdw = t1.id
group by departmentname, yzcd) f
on a.departmentname = f.departmentname
left join (select t1.departmentname, t2.yzcd, count(*) as count7
from formtable_main_19 t2, hrmdepartment t1
where yzcd = 1
and t2.bjdw = t1.id
group by departmentname, yzcd) g
on a.departmentname = g.departmentname
left join (select t1.departmentname, t2.yzcd, count(*) as count8
from formtable_main_19 t2, hrmdepartment t1
where yzcd = 2
and t2.bjdw = t1.id
group by departmentname, yzcd) l
on a.departmentname = l.departmentname
left join (select t1.departmentname, t2.wentxz, count(*) as count9
from formtable_main_19 t2, hrmdepartment t1
where wentxz = 0
and t2.bjdw = t1.id
group by departmentname, wentxz) i
on a.departmentname = i.departmentname
left join (select t1.departmentname, t2.wentxz, count(*) as count10
from formtable_main_19 t2, hrmdepartment t1
where wentxz = 1
and t2.bjdw = t1.id
group by departmentname, wentxz) j
on a.departmentname = j.departmentname
left join (select t1.departmentname, t2.wentxz, count(*) as count11
from formtable_main_19 t2, hrmdepartment t1
where wentxz = 2
and t2.bjdw = t1.id
group by departmentname, wentxz) k
on a.departmentname = k.departmentname
天善智能 2014-10-16
- 打赏
- 举报
修改SQL！！！
humanity 2014-10-14
- 打赏
- 举报
基本上里面有3个wentxz， 3个yzcd，3 个wtlb，它们不应该分成9个部分，
举例：
对于最后3个 wentxz 可以写成：
select departmentname
, sum(case when wentxz =0 then 1 else 0 end) as i
, sum(case when wentxz =1 then 1 else 0 end) as j
, sum(case when wentxz = 2 then 1 else 0 end) as k
from formtable_main_19 t2,hrmdepartment t1
where wentxz=2 and t2.bjdw=t1.id
group by departmentname
其它的3个也是类似，当它们的分组条件多数相同仅有最后一个不同时就可以这样把3个层次扁平化成1个层次。3合1，还可以做到9合1.
humanity 2014-10-14
- 打赏
- 举报
弄错了，比删除 SQL where 条件中的 wentxz = 2，我们在 select 后面对它进行过条件过滤，count 可以按条件地转换成为 sum(1)。
十林 2014-03-14
- 打赏
- 举报
代码有很大的改进空间。
cyliner 2014-03-03
- 打赏
- 举报
唉，仔细看了下你写的SQL，先补习下SQL吧，看下CASE 怎么用
cyliner 2014-03-03
- 打赏
- 举报
改存储过程呗
