ORACLE 数据库的题目 请各位大神帮我写一下 谢谢了

[qq_36734275]

请各位帮我看一下题目 用SQL 或者 存过 写出来 谢谢了 尤其是第二个题目

[碧水幽幽泉]

你自己先造些数据出来

[qq_36734275]

4楼 我问一下 我在数据库上运行第二题 发现 一个少有括号 我给了括号后 报缺少关键字 我看了一会 没有找到缺少什么 能帮我 看看吗 谢谢了

[qq_36734275]

而且 这个b.hours 中的b 并没有定义

[chengccy]

作业自己做

[minsic78]

引用 2 楼 chengccy 的回复:

作业自己做

[碧水幽幽泉]

SQL实现如下:

--创建员工基本信息表
create table Staffer as
select 1 id, 'Jack'   name, 25 age, 'IT'     dept from dual union all
select 2 id, 'Tom'    name, 26 age, 'IT'     dept from dual union all
select 3 id, 'Jessie' name, 27 age, 'Sale'   dept from dual union all
select 4 id, 'Rose'   name, 26 age, 'Sale'   dept from dual union all
select 5 id, 'Steven' name, 28 age, 'Market' dept from dual union all
select 6 id, 'John'   name, 22 age, 'Market' dept from dual

--创建考勤表
create table Worktime as
select 1 id, '8:45' intime, '18:17' outtime, 1 daynumber from dual union all
select 1 id, '9:00' intime, '19:00' outtime, 2 daynumber from dual

--题1：迟到、早退、缺勤员工姓名和日期，用union all关联查询
--1.查询迟到和早退的员工姓名和日期
select a.name,b.daynumber
  from Staffer a,Worktime b
 where a.id = b.id
   and ((case when a.intime > '9:00' then 1 else 0 end)+(case when a.outtime < '18:00' then 1 else 0 end))>0
union all
--2.查询缺勤的员工姓名和日期
select t1.name,t1.daynumber
  from(select a.id, a.name,b.daynumber
              from Staffer a,(select 1+rownum-1 daynumber from dual connect by rownum <= 30) b
            ) t1
    left join (select distinct id,daynumber from Worktime) t2 on t2.id = t1.id
  where t2.id is null;

--题2：各部门员工日均工作时间：
select a.dept,round(sum(b.hours)/30/count(*),2)  avg_work
 from Staffer a
 left join(select id, round(to_date(outtime,'yyyymmdd hh24:mi:ss') - to_date(intime,'yyyymmdd hh24:mi:ss') ,2) hours
            from(select id,to_char(trunc(sysdate),'yyyymmdd')||' '||intime as intime,to_char(trunc(sysdate),'yyyymmdd')||' '||outtime as outtime
                   from Worktime
                )
group by a.dept;

[碧水幽幽泉]

你试试这个:

select a.dept,round(sum(b.hours)/30/count(*),2)  avg_work
 from Staffer a
 left join(select id, round(to_date(outtime,'yyyymmdd hh24:mi:ss') - to_date(intime,'yyyymmdd hh24:mi:ss') ,2) hours
            from(select id,to_char(trunc(sysdate),'yyyymmdd')||' '||intime as intime,to_char(trunc(sysdate),'yyyymmdd')||' '||outtime as outtime
                   from Worktime
                )b
  on a.id = b.id
group by a.dept;

[bigdata-sb]

-- Create table
create table T_STAFFER
(
  id   INTEGER,
  name VARCHAR2(50),
  age  INTEGER,
  dept VARCHAR2(20)
);


-- Create table
create table T_WORKTIME
(
  id        INTEGER,
  intime    VARCHAR2(20),
  outtime   VARCHAR2(20),
  daynumber INTEGER
);

prompt Importing table t_staffer...
set feedback off
set define off
insert into t_staffer (ID, NAME, AGE, DEPT)
values (7369, 'SMITH', 29, '20');

insert into t_staffer (ID, NAME, AGE, DEPT)
values (7499, 'ALLEN', 26, '30');

insert into t_staffer (ID, NAME, AGE, DEPT)
values (7521, 'WARD', 25, '30');

insert into t_staffer (ID, NAME, AGE, DEPT)
values (7566, 'JONES', 25, '20');

insert into t_staffer (ID, NAME, AGE, DEPT)
values (7654, 'MARTIN', 34, '30');

insert into t_staffer (ID, NAME, AGE, DEPT)
values (7698, 'BLAKE', 33, '30');

insert into t_staffer (ID, NAME, AGE, DEPT)
values (7782, 'CLARK', 38, '10');

insert into t_staffer (ID, NAME, AGE, DEPT)
values (7788, 'SCOTT', 32, '20');

insert into t_staffer (ID, NAME, AGE, DEPT)
values (7839, 'KING', 29, '10');

insert into t_staffer (ID, NAME, AGE, DEPT)
values (7844, 'TURNER', 21, '30');

insert into t_staffer (ID, NAME, AGE, DEPT)
values (7876, 'ADAMS', 38, '20');

insert into t_staffer (ID, NAME, AGE, DEPT)
values (7900, 'JAMES', 21, '30');

insert into t_staffer (ID, NAME, AGE, DEPT)
values (7902, 'FORD', 36, '20');

insert into t_staffer (ID, NAME, AGE, DEPT)
values (7934, 'MILLER', 33, '10');

prompt Done.




insert into t_worktime 
select id,
       lpad(trunc(dbms_random.value(8, 10)), 2, '0') || ':' ||
       lpad(trunc(dbms_random.value(0, 59)), 2, '0') intime,
       
       lpad(trunc(dbms_random.value(17, 21)), 2, '0') || ':' ||
       lpad(trunc(dbms_random.value(0, 59)), 2, '0') outtime,
       daynum
  from t_staffer, (select level daynum from dual connect by level <= 30)
 order by id, daynum;

commit;



-- 随机删除几条数据， 制造缺勤
delete t_worktime
 where rowid in (select rid
                   from (select rowid rid,
                                daynumber,
                                trunc(dbms_random.value(1, 50)) random
                           from t_worktime t)
                  where random = daynumber);
commit;

-- 先看看每个员工这个月的出勤情况
select id, count(*) cnt from t_worktime group by id order by cnt;

-- 第一题的答案
-- 这种实现方式有一点不好的， 当员工数量比较多的时候， 例如 富士康500W员工， 这里就会有性能问题，
-- 因为这里使用笛卡尔积枚举出每个员工出勤30天的数据
with v_staff_days as
 (select /* 枚举出每个员工30天的考勤 */
   s.id, daynumber, s.dept
    from (select level daynumber from dual connect by level <= 30) days
   cross join (select id, dept from t_staffer) s)
--select * from v_staff_days order by id, daynumber
select t1.id,
       t1.daynumber,
       t1.dept,
       t2.intime,
       t2.outtime,
       (case
         when t2.id is null then
          '缺勤'
         when t2.intime > '09:00' and t2.outtime < '18:00' then
          '迟到、早退'
         when t2.intime > '09:00' then
          '迟到'
         when t2.outtime < '18:00' then
          '早退'
       end) reason
  from v_staff_days t1
  left join t_worktime t2
    on (t1.id = t2.id and t1.daynumber = t2.daynumber)
 where ((t2.id is null) --缺勤
       or (t2.intime > '09:00') --迟到
       or (t2.outtime < '18:00') --早退
       )
   -- and t1.id = 7900 -- 可加上这个过滤条件看看缺勤天数是否对的上
 order by t1.id, t1.daynumber;




----------------------------------------用存储过程来实现的话， 效率会高很多----------------------------------------
with v_base as
 (select t.*,
         lead(daynumber, 1, daynumber) over(partition by t.id order by t.daynumber) next_daynumber,
         (case
           when t.intime > '09:00' and t.outtime < '18:00' then
            3
           when t.intime > '09:00' then
            1
           when t.outtime < '18:00' then
            2
           else
            0
         end) status
    from t_worktime t)
select a.*,
       (case
         when next_daynumber - daynumber > 1 then
          1
         else
          0
       end) lost_range_mark
  from v_base a;


详见 存储过程实现思路.png


-- 搞一个表来存储异常考勤的数据
create table t_incorrect_log(
  staffid int,  -- 员工id
  daynumber int, -- 考勤日期
  status int, -- 是否迟到、早退 状态
  time1 int, --迟到时间(单位：分钟）
  time2 int, --早退时间(单位：分钟）
  lost int -- 是否缺勤  1 是 0 否
);



create procedure proc_analysis_incorrect_kq as
  type tbl_type is table of t_incorrect_log%rowtype index by pls_integer;
  tbl tbl_type; -- 用来存放考勤异常记录

  i pls_integer; -- 计数的

  v_final_date_prefix varchar2(20) := to_char(sysdate, 'yyyyMMdd');
  v_final_intime      date := to_date(v_final_date_prefix || ' 09:00',
                                      'yyyyMMdd hh24:mi');
  v_final_outtime     date := to_date(v_final_date_prefix || ' 18:00',
                                      'yyyyMMdd hh24:mi');
begin

-- 这里最好是分为两个for循环进行处理， 分别对 主SQL再添加过滤条件 status <> 0 表示迟到的  lost_range_mark <> 0 表示缺勤的
-- 这里我偷懒就只写一个了
  for r in (with v_base as
               (select t.*,
                      lead(daynumber, 1, daynumber) over(partition by t.id order by t.daynumber) next_daynumber,
                      (case
                        when t.intime > '09:00' and t.outtime < '18:00' then
                         3
                        when t.intime > '09:00' then
                         1
                        when t.outtime < '18:00' then
                         2
                        else
                         0
                      end) status
                 from t_worktime t)
              select a.*,
                     (case
                       when next_daynumber - daynumber > 1 then
                        1
                       else
                        0
                     end) lost_range_mark
                from v_base a) loop
  
    -- 判断迟到
    if r.status <> 0 then
      i := tbl.count + 1; --要+1
      tbl(i).staffid := r.id;
      tbl(i).daynumber := r.daynumber;
      tbl(i).status := r.status;
    
      tbl(i).time1 := (to_date(v_final_date_prefix || ' ' || r.intime,
                               'yyyyMMdd hh24:mi') - v_final_intime) * 24 * 60; --迟到时间(单位：分钟）
      tbl(i).time2 := (v_final_outtime -
                      to_date(v_final_date_prefix || ' ' || r.outtime,
                               'yyyyMMdd hh24:mi')) * 24 * 60; --早退时间(单位：分钟）
    
      if r.status = 2 then
        -- 只早退的不要记录迟到时间，否则会变成负数
        tbl(i).time1 := null;
      elsif r.status = 1 then
        -- 只迟到的不记录早退时间，否则会变成负数
        tbl(i).time2 := null;
      end if;
    
      tbl(i).lost := 0;
    end if;
  
    -- 判断缺勤
    if r.lost_range_mark = 1 then
      -- 找出缺勤的日期
      for j in r.daynumber + 1 .. r.next_daynumber - 1 loop
        dbms_output.put_line(r.daynumber || '~' || r.next_daynumber ||
                             ' ---> ' || j);
        i := tbl.count + 1; --要+1
        tbl(i).staffid := r.id;
        tbl(i).daynumber := j; -- 缺勤日
        tbl(i).status := null;
        tbl(i).time1 := null; --迟到时间(单位：分钟）
        tbl(i).time2 := null; --早退时间(单位：分钟）
        tbl(i).lost := 1;
      end loop;
    
    end if;
  
    -- 批量插入
    if i >= 50000 then
      forall x in 1 .. tbl.count
        insert into t_incorrect_log
          (staffid, daynumber, status, time1, time2, lost)
        values
          (tbl(x).staffid,
           tbl(x).daynumber,
           tbl(x).status,
           tbl(x).time1,
           tbl(x).time2,
           tbl(x).lost);
    
      commit;
      tbl.delete(); -- 一定要清空tbl
    end if;
  
  end loop;

  -- 最后一批数据可能没有达到50000条， 别漏了操作
  forall x in 1 .. tbl.count
    insert into t_incorrect_log
      (staffid, daynumber, status, time1, time2, lost)
    values
      (tbl(x).staffid,
       tbl(x).daynumber,
       tbl(x).status,
       tbl(x).time1,
       tbl(x).time2,
       tbl(x).lost);

  commit;

end;
/

select * from t_incorrect_log;
truncate table t_incorrect_log;  -- 每次测试都先删掉记录表的数据


-- 现在可在 t_incorrect_log 进行更多的分析了， 如看谁是迟到3巨头。。是否超出每个月限定的迟到次数、迟到时间等等。。。


-- 校验是否正确：
-- 运行完存储过程后， 查看 t_incorrect_log 表的数据量， 看和第一条SQL查询出来的数据量是否一致。
-- 也可单独看某个员工的出勤，来和 t_incorrect_log 表的数据进行比对




-- 第二题的答案：每个员工每个月工作总时长
select b.dept, sum(a.day_worktime) / count(distinct a.id) staff_cnt / 30
  from (select x.id,
               (to_date(x.outtime, 'hh24:mi') - to_date(x.intime, 'hh24:mi')) * 24 day_worktime
          from t_worktime x) a
 inner join t_staffer b
    on (a.id = b.id)
 group by b.dept;