//插入QYJob
INSERT INTO QYJob(AgencyID,DepartmentID,JobName)
select * from 
(SELECT t4.AgencyID,t5.DepartmentID, t1.position
	FROM QYEmployee  t1
	INNER JOIN QYTemp t2 ON t1.department=t2.QYID--部门
	INNER JOIN dbo.QYTemp t3 ON t2.QYParentid=t3.QYID--组织
	INNER JOIN dbo.AgencyTable t4 ON t3.QYID = t4.接口层级ID
	INNER JOIN dbo.DepartmentTable t5 ON t2.QYID = t5.接口部门ID AND t4.AgencyID = t5.AgencyID
	GROUP BY t4.AgencyID,t5.DepartmentID, t1.position
)t where  DepartmentID=628 and   AgencyID=66;


SELECT AgencyID ,DepartmentID ,JobName ,10000,0,GETDATE(),'00000000-0000-0000-0000-000000000000',JobID
	FROM (
		SELECT t1.*,ROW_NUMBER() OVER(PARTITION BY t1.AgencyID,t1.DepartmentID,t1.JobName ORDER BY t1.JobID DESC) AS Idx
		FROM QYJob t1
		LEFT JOIN JobTable t2 ON t2.接口部门ID = t1.jobID
		WHERE t2.JobID IS NULL      
		--WHERE t1.AgencyID= 165 and t1.DepartmentID=596
	) t;

select *  FROM  JobTable WHERE 接口部门ID=1332 or 接口部门ID=3735;


SELECT * from QYJob   where  DepartmentID=628 and   AgencyID=66  and JobName='厨房经理';

//检查查询关系
select  UserGuid,WeiXinUserID  from EmployeeTable  where  Username='18874174220';

select OA门店编码 from 企业微信OA对照表 where 人员UserGuid='163F249C-1576-464F-BA02-74FE12FBDBFA';

select userid,POSITION,department FROM dbo.QYEmployee where mobile='18874174220';

select JobName,DepartmentID,AgencyID from JobTable where JobName='厨房经理';

select QYParentid from  QYTemp  where QYID='4056223717';

select QYID from  QYTemp  where QYParentid='705';

select  AgencyID  from AgencyTable where 接口层级ID='705';

select  * from  DepartmentTable  where 接口部门ID='4056223717' and  AgencyID='66';

select  *  from  JobTable  where  DepartmentID=628 and   AgencyID=66

//示例数据
select  t5.DepartmentName,T55.OA门店编码 
	FROM dbo.QYEmployee t1 
	INNER JOIN QYTemp t2 ON t1.department=t2.QYID--部门
	INNER JOIN dbo.QYTemp t3 ON t2.QYParentid=t3.QYID--组织
	INNER JOIN dbo.AgencyTable t4 ON t3.QYID = t4.接口层级ID
	INNER JOIN dbo.DepartmentTable t5 ON t2.QYID = t5.接口部门ID AND t4.AgencyID = t5.AgencyID
	INNER JOIN dbo.JobTable t6 ON t1.position=t6.JobName AND t5.DepartmentID = t6.DepartmentID AND t4.AgencyID = t6.AgencyID	
	INNER JOIN EmployeeTable t7 ON t1.userid = t7.WeiXinUserID
  LEFT JOIN [mssql-db1-m.gtmsh.com].oa.dbo.org_member T44 ON T1.mobile=T44.EXT_ATTR_1 collate Chinese_PRC_BIN
	--LEFT JOIN OPENROWSET('SQLOLEDB', 'localhost'; 'webadmin'; 'wygswygs',v51.dbo.[org_member]) T44 ON T1.mobile=T44.EXT_ATTR_1
	LEFT JOIN 企业微信OA对照表 T55 ON t7.UserGuid=T55.人员UserGuid 
	--LEFT JOIN #一级部门 T8 ON T1.department=T8.QYID
	WHERE  mobile='18874174220';

select top 1 * from  企业微信OA对照表;

select top  1 *  from  QYEmployee;

select  *  from EmployeeTable where [人员角色]='跟进人员' and  一级部门 like  '%店%' and IsValid=1;