34,587
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #ECN
(PID VARCHAR(10),
DEPT_P VARCHAR(10),
ID VARCHAR(10))
INSERT INTO #ECN
SELECT 'EC123','A1','AA1' UNION ALL
SELECT 'EC124','B2','AA2' UNION ALL
SELECT 'ED125','A3','AA3' UNION ALL
SELECT 'EC126','A4','AA4'UNION ALL
SELECT 'EC127','B4','AA5'UNION ALL
SELECT 'EC128','A6','AA6'
CREATE TABLE #RULES
(PID VARCHAR(10),
DEPT_P VARCHAR(10),
ID VARCHAR(10))
INSERT INTO #RULES
SELECT 'EC','A1','AA2' UNION ALL
SELECT 'MP','A2','AA1' UNION ALL
SELECT '','A3','' UNION ALL
SELECT '','A4',''UNION ALL
SELECT '','A6',''
SELECT A.*
FROM #ECN A JOIN #RULES B ON LEFT(A.PID,2)=B.PID AND A.DEPT_P=B.DEPT_P
UNION
SELECT A.*
FROM #ECN A JOIN #RULES B ON A.ID LIKE '%'+B.ID+'%'
select *from (select * from Rules where ECN.文号=ECNnum_CLass or CHARINDEX(ECNnum_FcLass ,ECN.机种名)>0) a join ECN on a.ECNnum_rules=LEFT(ECN.ECNNum,2)
select * from ECN where exists ( select * from Rules where ECNnum_Rules = left( ECNNum, 2 ));
SELECT A.*
FROM ECN A JOIN RULES B ON LEFT(A.ECNNUM,2)=B.ECNNUM_RULES AND A.文号=B.ECNnum_Class
UNION
SELECT A.*
FROM ECN A JOIN RULES B ON A.机种名 LIKE '%'+B.ECNnum_Fclass+'%'