select count(id) from
(
select a.id, a.tjmc 考核名称, a.dwmc 单位名称 ,a.kssj 开始时间,a.jssj 结束时间,
sum(decode(a.tjx,'10001',a.tjz,0)) 正常出勤,
sum(decode(a.tjx,'10002',a.tjz,0)) 迟到,
sum(decode(a.tjx,'10003',a.tjz,0)) 早退,
sum(decode(a.tjx,'10004',a.tjz,0)) 病假,
sum(decode(a.tjx,'10005',a.tjz,0)) 脱岗,
sum(decode(a.tjx,'1006',a.tjz,0)) 缺勤
from cqtj a,
(select dwid from qwgl_t_dwxx_b START WITH dwid = 1 CONNECT BY SJDW = PRIOR DWID) b
where a.dwid(+)=b.dwid and a.tjlx='y' group by id,tjmc,dwmc,kssj,jssj,tjlx
)
----------------------------------
以上SQL对数据库配置有要求吗?
同是9i库,有个数据库里就是无法执行,不报错,就是长时间的等待,就像死机一样,
而在其他库却可以正常执行,没有任何问题。
(
select a.id, a.tjmc 考核名称, a.dwmc 单位名称 ,a.kssj 开始时间,a.jssj 结束时间,
sum(decode(a.tjx,'10001',a.tjz,0)) 正常出勤,
sum(decode(a.tjx,'10002',a.tjz,0)) 迟到,
sum(decode(a.tjx,'10003',a.tjz,0)) 早退,
sum(decode(a.tjx,'10004',a.tjz,0)) 病假,
sum(decode(a.tjx,'10005',a.tjz,0)) 脱岗,
sum(decode(a.tjx,'1006',a.tjz,0)) 缺勤
from cqtj a,
(select dwid from qwgl_t_dwxx_b START WITH dwid = 1 CONNECT BY SJDW = PRIOR DWID) b
where a.dwid(+)=b.dwid and a.tjlx='y' group by id,tjmc,dwmc,kssj,jssj,tjlx
)
----------------------------------
以上SQL对数据库配置有要求吗?
同是9i库,有个数据库里就是无法执行,不报错,就是长时间的等待,就像死机一样,
而在其他库却可以正常执行,没有任何问题。
估计主要是
START WITH dwid = 1 CONNECT BY SJDW = PRIOR DWID引起的
长时间没有反应,可能是计划不对。
中间的子查询
select a.id, a.tjmc 考核名称, a.dwmc 单位名称 ,a.kssj 开始时间,a.jssj 结束时间,
sum(decode(a.tjx,'10001',a.tjz,0)) 正常出勤,
sum(decode(a.tjx,'10002',a.tjz,0)) 迟到,
sum(decode(a.tjx,'10003',a.tjz,0)) 早退,
sum(decode(a.tjx,'10004',a.tjz,0)) 病假,
sum(decode(a.tjx,'10005',a.tjz,0)) 脱岗,
sum(decode(a.tjx,'1006',a.tjz,0)) 缺勤
from cqtj a,
(select dwid from qwgl_t_dwxx_b START WITH dwid = 1 CONNECT BY SJDW = PRIOR DWID) b
where a.dwid(+)=b.dwid and a.tjlx='y' group by id,tjmc,dwmc,kssj,jssj,tjlx 查询都是可以的,
但是一count就不行。
这个子查询引起的,
注释掉后就可以正常,
可是在其他库都是执行正常的,
而这个select dwid from qwgl_t_dwxx_b START WITH dwid = 1 CONNECT BY SJDW = PRIOR DWID语句本身执行没问题啊。