SELECT
TCStrategy.PlanSchedule as TCStrategy__PlanSchedule,
TCStrategy.DepartmentID as TCStrategy__DepartmentID,
BT_ProType.TypeDescription as TCStrategy__ProTypeName,
TCStrategy.ProjectName as TCStrategy__ProjectName,
ServiceRequistion.SRName as TCStrategy__SRIDKey,
TCStrategy.TenderID as TCStrategy__TenderID
FROM
TCStrategy
left join ServiceRequistion on TCStrategy.SRIDKey = ServiceRequistion.IDKey
left join BT_ProType on TCStrategy.ProTypeName = BT_ProType.IDKey
WHERE
OR charindex(','+convert(varchar,(SELECT dbo.f_GetDepartmentIDbyEmployeeID(TCStrategy.CreateBy)
FROM TCStrategy b where TCStrategy.TenderID=b.TenderID))+',',',Dep_10_02,')>0
-------------------------------------------------
convert(varchar,(SELECT dbo.f_GetDepartmentIDbyEmployeeID(TCStrategy.CreateBy)
FROM TCStrategy b where TCStrategy.TenderID=b.TenderID))这句求出的是创建单据者的部门,可能包含2个部门,也就是说可能是 Dep_10_02,Dep_10_03.我需要的是只要它有一个满足在后面求出的集合中就可以了.后面的也是一个以","分割的求出的部门集合,也有可能包含2,3个.也就是登陆者作为领导的部门.
总结的说一下,就是领导能看见下属的部门的员工创建的单据.领导可能是多个部门的领导,员工可能是多个部门的员工.
,Dep_10_02, --->是我求出的登陆者作为领导的部门.可能包含多个.(集合一)convert(varchar,(SELECT dbo.f_GetDepartmentIDbyEmployeeID(TCStrategy.CreateBy)
FROM TCStrategy b where TCStrategy.TenderID=b.TenderID))
--->求出创建单据者的部门.可能也包含多个.以","分割的集合,只要这个集合有一个存在于上面的那个 集合一 中就满足条件.
求解,谢谢.
TCStrategy.PlanSchedule as TCStrategy__PlanSchedule,
TCStrategy.DepartmentID as TCStrategy__DepartmentID,
BT_ProType.TypeDescription as TCStrategy__ProTypeName,
TCStrategy.ProjectName as TCStrategy__ProjectName,
ServiceRequistion.SRName as TCStrategy__SRIDKey,
TCStrategy.TenderID as TCStrategy__TenderID
FROM
TCStrategy
left join ServiceRequistion on TCStrategy.SRIDKey = ServiceRequistion.IDKey
left join BT_ProType on TCStrategy.ProTypeName = BT_ProType.IDKey
WHERE
OR charindex(','+convert(varchar,(SELECT dbo.f_GetDepartmentIDbyEmployeeID(TCStrategy.CreateBy)
FROM TCStrategy b where TCStrategy.TenderID=b.TenderID))+',',',Dep_10_02,')>0
-------------------------------------------------
convert(varchar,(SELECT dbo.f_GetDepartmentIDbyEmployeeID(TCStrategy.CreateBy)
FROM TCStrategy b where TCStrategy.TenderID=b.TenderID))这句求出的是创建单据者的部门,可能包含2个部门,也就是说可能是 Dep_10_02,Dep_10_03.我需要的是只要它有一个满足在后面求出的集合中就可以了.后面的也是一个以","分割的求出的部门集合,也有可能包含2,3个.也就是登陆者作为领导的部门.
总结的说一下,就是领导能看见下属的部门的员工创建的单据.领导可能是多个部门的领导,员工可能是多个部门的员工.
,Dep_10_02, --->是我求出的登陆者作为领导的部门.可能包含多个.(集合一)convert(varchar,(SELECT dbo.f_GetDepartmentIDbyEmployeeID(TCStrategy.CreateBy)
FROM TCStrategy b where TCStrategy.TenderID=b.TenderID))
--->求出创建单据者的部门.可能也包含多个.以","分割的集合,只要这个集合有一个存在于上面的那个 集合一 中就满足条件.
求解,谢谢.
用户b是部门 dep1,dep3,dep4的员工
现在用户b创建了一条单据用户a应该能看见,如果像我上面用 charindex来求的话就错了.因为上面的将生成 charindex(',dep1,dep3,dep4',',dep1,dep2,')>0其实是满足条件的,这么一求就不满足了
CREATE FUNCTION CompareStr(@STR1 VARCHAR(100), @STR2 VARCHAR(100))
RETURNS TINYINT
AS
BEGIN
DECLARE @PLACE INT
DECLARE @RE INT
SET @STR1 = ',' + @STR1 + ','
SET @STR2 = @STR2 + ','
SET @RE = 0
SET @PLACE = CHARINDEX(',', @STR2)
WHILE @PLACE > 0
BEGIN
IF CHARINDEX(',' + LEFT(@STR2, @PLACE), @STR1) > 0
BEGIN
SET @RE = 1
SET @PLACE = 0
END
ELSE
BEGIN
SET @STR2 = RIGHT(@STR2, LEN(@STR2) - @PLACE)
SET @PLACE = CHARINDEX(',', @STR2)
END
END
RETURN @RE
END
GO
--例如
IF dbo.CompareStr('dep1,dep3,dep4', 'dep1,dep2') = 1
PRINT 'YES'
ELSE
PRINT 'NO'
--结果是YES
SELECT distinct
TCStrategy.PlanSchedule as TCStrategy__PlanSchedule,
TCStrategy.DepartmentID as TCStrategy__DepartmentID,
BT_ProType.TypeDescription as TCStrategy__ProTypeName,
TCStrategy.ProjectName as TCStrategy__ProjectName,
ServiceRequistion.SRName as TCStrategy__SRIDKey,
TCStrategy.TenderID as TCStrategy__TenderID
FROM
TCStrategy
left join ServiceRequistion on TCStrategy.SRIDKey = ServiceRequistion.IDKey
left join BT_ProType on TCStrategy.ProTypeName = BT_ProType.IDKey
inner join (select 部门id from 部门表 where charindex(部门id,',Dep_10_02,') > 0) t_bumen on charindex(部门ID,convert(varchar,(SELECT dbo.f_GetDepartmentIDbyEmployeeID(TCStrategy.CreateBy) FROM TCStrategy b where TCStrategy.TenderID=b.TenderID))) > 0