我可以在不使用游标的情况下从单个SQL联接返回多个不匹配的结果吗

Question

我有两个表。

员工

EmployeeID, EmployeeName, DocumentType

和

文档类型

DocumentTypeID, DocumentType

有多个雇员和多个文档类型。

对于每个员工，我都试图显示哪些DocumentTypes不存在。

我似乎不能为每个雇员ID/雇员名称都这样做。我只能得到一个不适用于所有员工的DocumentTypes列表。

我可以在没有光标穿过每个EmployeeID的情况下完成这项工作吗？

Answer 1

编辑更好的版本

select distinct e.EmployeeID, e.EmployeeName, t.DocumentTypeID, t.DocumentType
from Employee e
cross join DocumentType t
left join Employee e2 on e2.EmployeeID = e.EmployeeID and t.DocumentTypeID = e2.DocumentTypeID
where e2.EmployeeID is null

原件-这是有效的，但感觉不是最优雅的解决方案

select distinct e.EmployeeID, e.EmployeeName, dt.DocumentTypeID, dt.DocumentType
from Employee e
outer apply (
    select * from DocumentType t
    where not exists (
        select 1 
        from Employee e2 
        where e2.DocumentTypeID = t.DocumentTypeID 
        and e2.EmployeeID = e.EmployeeID)
) dt

Answer 2

我想您正在寻找类似的数据

EmployeeID EmployeeName DocumentTypeNotMapped
1          abc          doctype1, doctype3, doctype4
2          def          doctype3, doctype2, doctype7

使用查询

SELECT ET.EmployeeID, ET.EmployeeName,
SELECT LEFT(DocumentType, LEN(DocumentType) - 1)
FROM (
    SELECT DISTINCT DocumentType + ', '
    FROM DocumentTypeTable 
    WHERE DocumentType != ET.DocumentType 
    FOR XML PATH ('')
  ) D (DocumentType)
FROM EmployeeTable ET

Answer 3

假设您使用的是sql server

;with cte as (
select E.EmployeeID, E.DocumentType,D.DocumentTypeID from Employee E
 left outer join Document D
on E.DocumentType<>D.DocumentTypeID),
cte1 as (select EmployeeID,DocumentTypeID  from cte
group by  EmployeeID,DocumentTypeID
having count(*)>1)
select * from cte1
union all
select EmployeeID,DocumentTypeID from cte where EmployeeID not in
(select EmployeeID from cte1)
order by EmployeeID,DocumentTypeID