我有下面的表T1
RL OR FVALU TVALU
R1 O1 3291
R1 O1 3002
R1 O1 3010
R2 O1 2000
和另一个表T2如下:
RL OR FVALU TVALU
R1 O1 3291
R1 O1 3000 3005
R1 O1 5000
R2 O1 *
预期输出应如下:
RL OR FVALU TVALU MATCHORDOESNOT
R1 O1 3291 MATCH
R1 O1 3002 MATCH
R1 O1 3010 DONOTMATCH
R2 O1 2000 MATCH
================================================== =============我该如何匹配从T1到T2的FVALU和TVALU字段中的值?
在上述情况下,T1和T2表之间也应匹配3002和3291 *值应与2000匹配。前两列可以具有连接。另外,注意TVALU有时有价值,有时为Null
我在下面的查询中尝试过并且无法正常工作: SELECT T1.RL, T1.[OR], T1.FVALU, T1.TVALU, IIf(InStr(([T2]![FVALU]),"*")>0,"MATCH",IIf([T1]![FVALU] Between ([T2]![FVALU]) And (IIf([T2]![FVALU] Is Null,[T2]![FVALU],[T2]![FVALU])),"MATCH","DONOTMATCH")) AS MATCHORDOESNOT FROM T1 INNER JOIN T2 ON (T1.[OR] = T2.[OR]) AND (T1.RL = T2.RL);
结合LEFT
表和ON
子句中的所有条件:
SELECT DISTINCT T1.*,
IIF(T2.RL IS NULL, 'DONOTMATCH', 'MATCH') AS MATCHORDOESNOT
FROM T1 LEFT JOIN T2
ON T2.RL = T1.RL AND T2.[OR] = T1.[OR]
AND (T2.FVALU = T1.FVALU OR (INSTR(T2.FVALU, '*') > 0) OR (T1.FVALU BETWEEN T2.FVALU AND T2.TVALU))
结果:
RL OR FVALU TVALU MATCHORDOESNOT
R1 O1 3002 MATCH
R1 O1 3010 DONOTMATCH
R1 O1 3291 MATCH
R2 O1 2000 MATCH
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句