[SqlServer]多表查询 人员未读情况

前置信息

某个业务，能够发布给相关用户（发布时可以直接指定具体用户，也可以指定到某些岗位或者某个部门组织），用户的查阅情况会被记录下来，需要统计出该业务未读数量，具体如下

表构成：

PS：当某条业务在TableT内有数据，就代表这条业务 仅限这些对象可看；而当TableT内没有 该业务 对应的对象数据时，就代表全员可看

统计需求

维度：用户

指标：未读数量

范围：进行中的业务

排序：

表现：

SQL: 

select COUNT(distinct TM.TID) Num,TU.UserID 
from 
UsrRl TR 
left join User TU on TU.UserID = TR.UserID 
left join Rol TL on TL.RoleID = TR.RoleID 
---内连接 业务的接收对象表 通过 三个or 条件 根据不同对象类别 来判断用户是否可读 
inner join TableT TD on ((TR.UserID = TD.BodyID and TD.BodyType = 'U') or (TR.RoleID = TD.BodyID and TD.BodyType = 'R') or (TL.OrgID like TD.BodyID+'%' and TD.BodyType = 'O'))
inner join Table TM on TD.TID = TM.TID OR (Not Exists (Select 1 From PINtcT TT Where TT.TID = TM.TID))--存在接收对象和不存在发布对象的
where 
--用户未停用 
TU.IsClose <>'Y'
--发布中的业务 通过SDate和EDate 判断是否过期 getDate()获取到的是今天
AND TM.Status = 'O' and TM.IsClose = 'N' and TM.SDate <= getDate() and TM.EDate >= getDate()
--排除已读
and Not Exists ( Select 1 From TableR TB Where TB.TID = TM.TID And TB.UserID = TU.UserID )
Group By TU.UserID

如果上面的几个 join把自己搞晕了，可以参考下面带子查询的

select 
COUNT( distinct TM.TID) Num,U.UserID
from
(
    --通过关系表 查询得到 所有用户的 UserID，该用户所拥有的所有岗位，还有这些岗位所属组织
	select TR.UserID,TR.RoleID,TL.OrgID from 
	UsrRl TR 
	left join User TU on TU.UserID = TR.UserID 
	left join Rol TL on TL.RoleID = TR.RoleID 
	where TU.IsClose <>'Y' and TL.IsClose <> 'Y' --未停用的用户 未停用的岗位
)U
--内连接 业务的接收对象表 通过 三个or 条件 以防漏掉数据
inner join TableT TD  
on ((U.UserID = TD.BodyID and TD.BodyType = 'U') or (U.RoleID = TD.BodyID and TD.BodyType = 'R') or (U.OrgID like TD.BodyID+'%' and TD.BodyType = 'O')) 
--内连接业务表 存在接收对象和不存在接收对象的
inner join Table TM on TD.TID = TM.TID OR (Not Exists (Select 1 From TableT TT Where TT.TID = TM.TID))
where 
--发布中的业务
 TM.DocStatus = 'O' and TM.IsClose = 'N' and TM.SDate <= getDate() and TM.EDate >= getDate()
--排除已读
and Not Exists ( Select 1 From TabelR TB Where TB.TID = TM.TID And TB.UserID = U.UserID )
Group By U.UserID

上面最主要的是 判断用户是否可读：

这里用的是 【用户岗位组织 （视图）】 内连接 【业务指定查看对象表】，通过三个or来判断是否可读

例如：

一个用户 UserID是 A001,岗位是:开发 D001, 所属组织是 技术部 1010.

如果某条业务指定 查看对象是【开发岗位】，就要用 User.RoleID = TableT.BodyID 来判断，

而如果此条业务 指定的查看对象是【技术部】【产品部】，就要用 【用户岗位组织 （视图）】.OrgID like TableT.BodyID + '%' ;

上述查组织时用like原因

是因为 组织编码 符合树形编码：

上级编号+本级编号

例如：XX公司 OrgID为10，这个公司里的技术部 OrgID就可以为1010 （第一个10是上级组织编码，第二个10是本级组织编码），若是和技术部同级的像销售部，可以是 1020。

是不是通过组织编码就能直接看出 各组织间的上下级关系。