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

Question

算出每个员工的拜访次数
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');

Answer 1

这个题目没说得太明白，但是看上去好像是拜访表的第二个字段，用逗号分隔了员工编号，有一个逗号时表示拜访了两次，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(+)

---

根据题主补充信息来看，拜访表里的并不是大家以为的拜访人和被拜访人，拜访表里存的其实是拜访计划，两个字段的含义分别是拜访领队和拜访成员，也就是说，其实是要统计拜访表中，每个人员出现的次数，也就是说，要把逗号里的内容进行拆分，把所有的工号放到一列中去，此时才能用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

---

我知道你们出题人的意思了，考的是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

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

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

---

出题人给出的答案用了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;