掉头发,, 2022-03-16 11:05 采纳率: 100%
浏览 141
已结题

如何在不用子查询的情况下使用SQL写出

算出每个员工的拜访次数
CREATE TABLE A(EMP_NO NUMBER(10),EMP_NAME VARCHAR(20));
--员工表
insert into A (EMP_NO, EMP_NAME)values ('7823', '张三');
insert into A (EMP_NO, EMP_NAME)values ('1112', '李四');
insert into A (EMP_NO, EMP_NAME)values ('1234', '王二');
insert into A (EMP_NO, EMP_NAME)values ('1111', '小明');
insert into A (EMP_NO, EMP_NAME)values ('5555', '小鹏');
CREATE TABLE B(EMP_NO VARCHAR(10),ACC_ID VARCHAR(20));--拜访表
insert into B (EMP_NO, ACC_ID)values ('7823', '5555');
insert into B (EMP_NO, ACC_ID)values ('1234', '1112,7823');
insert into B (EMP_NO, ACC_ID)values ('1112', '7823,1234');
insert into B (EMP_NO, ACC_ID)values ('5555', '1112,7823,1234,5555');

  • 写回答

2条回答 默认 最新

  • DarkAthena ORACLE应用及数据库设计方案咨询师 2022-03-16 13:56
    关注

    这个题目没说得太明白,但是看上去好像是拜访表的第二个字段,用逗号分隔了员工编号,有一个逗号时表示拜访了两次,2个逗号表示拜访了3次,因此只需要统计出逗号的个数就行了。
    另外,由于存在还有员工没有拜访的情况,因此需要外关联,并且将空值转换成0,
    还有,oracle数据库中不建议使用varchar类型,而是应该使用varchar2类型,
    这个题中两个表的关联字段类型不一样,关联的时候建议进行类型转换,否则当B表工号出现非纯数字的内容时会报错,就算没报错也影响关联效率,因为它需要隐式地对全表进行一次to_number处理,这个时候显示指定to_char更加可控,而且可以避免报错

    select a.*,
           nvl(length(acc_id) - length(replace(acc_id, ',')) + 1, 0) 拜访数
      from test_20220316_A a, test_20220316_B b
     where to_char(a.EMP_NO) = b.EMP_NO(+)
    

    img


    根据题主补充信息来看,拜访表里的并不是大家以为的拜访人和被拜访人,拜访表里存的其实是拜访计划,两个字段的含义分别是拜访领队和拜访成员,也就是说,其实是要统计拜访表中,每个人员出现的次数,也就是说,要把逗号里的内容进行拆分,把所有的工号放到一列中去,此时才能用count。
    但是这里存在一个问题,不同数据库的转换方式不一样,又要求所有数据库得通用,的确是个难题了,稍等
    先给个oracle的

    
     with t as 
     (select listagg(emp_no||','||acc_id,',') within group(order by 1) a from test_20220316_B),
     cte(e,lvl) as (
     select substr(a,1,instr(a,',')-1) e , 1 lvl from t
     union all
     select substr(a,instr(a,',',1,lvl)+1, instr(a,',',1,lvl+1)-instr(a,',',1,lvl)-1) ,lvl+1 from 
     cte ,t where instr(a,',',1,lvl+1) <>0
     )
    select a.EMP_NO, a.EMP_NAME, count(e)
      from cte, test_20220316_A a
     where to_char(a.EMP_NO) = cte.e(+)
     group by a.EMP_NO, a.EMP_NAME
    

    img


    我知道你们出题人的意思了,考的是like join产生的笛卡尔积,但是这样会存在一点问题,因为不确定是否存在工号长度不一,比如123和1234,两者可能会匹配上,需要做一些特殊处理

    select a.emp_no,a.emp_name,count(b.emp_no)
      from test_20220316_A a
      left join test_20220316_b b
        on b.emp_no||','||b.acc_id like '%' || a.EMP_NO || '%'
     group by a.emp_no,a.emp_name
    

    img

    加上分隔符特殊处理,避免歧义

    select a.emp_no,a.emp_name,count(b.emp_no)
      from test_20220316_A a
      left join test_20220316_b b
        on ','||b.emp_no||','||b.acc_id||',' like '%'||','||a.EMP_NO||','|| '%'
     group by a.emp_no,a.emp_name
    

    img


    出题人给出的答案用了instr函数,如下

    SELECT T.EMP_NO,T.EMP_NAME,COUNT(T1.EMP_NO) AS "拜访次数"
    FROM A T
    LEFT JOIN B T1
    ON T.EMP_NO=T1.EMP_NO OR INSTR(T1.ACC_ID,T.EMP_NO)>0
    GROUP BY T.EMP_NO,T.EMP_NAME;
    

    INSTR函数获取第二个参数在第一个参数中出现的位置,如果没有出现,那么返回的是0,这里写了大于0 ,其实和我上面写的like差不多一个意思,但这个答案的歧义问题还是得加上标识符,要改成像下面这样才算没有漏洞,因为员工工号只会为数字,加上任意非数字的分隔符能准确识别字符串的截断位置

    SELECT T.EMP_NO,T.EMP_NAME,COUNT(T1.EMP_NO) AS "拜访次数"
    FROM test_20220316_A T
    LEFT JOIN test_20220316_b T1
    ON T.EMP_NO=T1.EMP_NO OR INSTR(concat(',',T1.ACC_ID),concat(',',T.EMP_NO))>0
    GROUP BY T.EMP_NO,T.EMP_NAME;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论 编辑记录
查看更多回答(1条)

报告相同问题?

问题事件

  • 系统已结题 3月24日
  • 已采纳回答 3月16日
  • 创建了问题 3月16日

悬赏问题

  • ¥15 Jenkins+k8s部署slave节点offline
  • ¥15 微信小游戏反编译后,出现找不到分包的情况
  • ¥15 如何实现从tello无人机上获取实时传输的视频流,然后将获取的视频通过yolov5进行检测
  • ¥15 WPF使用Canvas绘制矢量图问题
  • ¥15 用三极管设计一个单管共射放大电路
  • ¥15 孟德尔随机化r语言运行问题
  • ¥15 pyinstaller编译的时候出现No module named 'imp'
  • ¥15 nirs_kit中打码怎么看(打码文件是csv格式)
  • ¥15 怎么把多于硬盘空间放到根目录下
  • ¥15 Matlab问题解答有两个问题