我现在有一张表,建表语句如下
CREATE TABLE REPRECORD(
ID INT NOT NULL,
ASSIGNID INT NOT NULL,
CATE1NAME VARCHAR2(20) NOT NULL,
CATE2NAME VARCHAR2(20),
WORKERID VARCHAR2(20) NOT NULL,//工人ID
WORKERNAME VARCHAR2(20) NOT NULL,//工人姓名
CHARGEID VARCHAR2(20) NOT NULL,
ARRANGETIME DATE NOT NULL,//维修派出时间
FINISHTIME DATE,//维修完成时间
SCORE1 INT,
SCORE2 INT,
SCORE3 INT,
REPRESULTID VARCHAR2(20) NOT NULL,//维修状态,1为成功,2为失败
FREASON VARCHAR2(40),
PRIMARY KEY (ID),
FOREIGN KEY(ASSIGNID) REFERENCES REPREQUEST(ID),
FOREIGN KEY(WORKERID) REFERENCES USERS(DBUSER)
);我现在在写一条sql语句,包括以下几个内容:工人id,该工人维修完成工时,该工人维修成功个数,该工人维修失败个数,总个数。
完成时间减去派出时间就是这个工人维修一个成功的时间。工时的意思就是我一个工人维修成功的这些总时间加起来除以成功的个数。比如一个工人有5个任务,他有3个成功了,第一个耗费2小时,第二个耗费3小时,第三个耗费4小时,他的平均工时就是(2+3+4)/3.这个工人对应的一条表数据就是工人id(假设为001),(2+3+4)/3,3,2,5。
我曾经求救过这个问题,得到了一个答案:
select succ_sub.WORKERID "工人id", 
       succ_sub.succ_cnt "维修成功个数", 
       succ_sub.averagetime "维修完成工时", 
       nvl(fail_sub.fail_cnt, 0) "维修失败个数",
       (succ_sub.succ_cnt+nvl(fail_sub.fail_cnt, 0)) "总个数"
  from
       (select succ_sub.WORKERID WORKERID, count(1) succ_cnt, sum(succ_sub.workhours)/count(1) averagetime
          from
               (select WORKERID, WORKERNAME, round((FINISHTIME-ARRANGETIME)*24) workhours from test_a
                 where REPRESULTID = '1') succ_sub
              group by succ_sub.WORKERID
       )  succ_sub,
       (select WORKERID, count(1) fail_cnt from test_a where REPRESULTID = '2' group by WORKERID) fail_sub
 where succ_sub.WORKERID = fail_sub.WORKERID(+)
这句sql是正确的,可以正常显示一个员工的维修情况。但是在一些极端的情况下,比如说这个员工维修了5次,但都是失败的,这句sql是检索不出来的。因为succ_sub为空,只有fail_sub里有记录。我想改进一下这条SQL,可以使得在能正常显示一般情况下,还可以对维修都是失败的人员进行统计,将他的平均工时置为-1.
举例来说,有两个员工,一个成功了3次,失败了2次。另外一个失败了5次,我想改进sql后生成如下数据:
(001,3,1.5,2,5),(002,0,-1,5,5)。第一条记录的意思就是001的员工成功3次,失败2次,平均工时为1.5,总共有5个记录。第二条记录的意思就是002的员工成功0次,失败5次,平均工时为-1,总共有5次记录。这第二条记录我原先的sql是检索不出来的。我希望可以将这条也检索出来。还有一个要求,我还想得到员工的姓名,就是WORKERNAME.原来的sql里没有取出这条数据。请各位帮忙看看,多谢了。

解决方案 »

  1.   

    从优化的角度来说,上表应该分成三个表,分别是工人信息表、任务表和两表的关联表,这才符合BCNF范式。运行以上你的查询的也更清晰。
      

  2.   

    full outer join 就可以了。
    内连接会滤掉不能匹配的数据。
      

  3.   

    select a.id, --工号
           a.workername, --姓名
           decode(sum(decode(a.represultid, 1, 1, 0)),
                  0,
                  -1,
                  round(sum(decode(a.represultid,
                                   1,
                                   (a.finishtime - a.arrangetime) * 24,
                                   0)) / sum(decode(a.represultid, 1, 1, 0)),
                        2)), --平均工时
           
           sum(decode(a.represultid, 1, 1, 0)), --成功数
           sum(decode(a.represultid, 1, 0, 1)), --失败数
           count(1) --总数
      from REPRECORD a
     group by a.id, a.workername