请教一SQL(关联值可能为空)
TABLEA (AID PK,A1 NOT NULL,A2,A3,REMARKA)
TABLEB (BID PK,B1 NOT NULL,B2,B3,REMARKB)
如果TABLEA.A1,TABLEA.A2,TABLEA.A3,TABLEB.B1,TABLEB.B2,TABLEB.B3 有值的情况下
两表可以这样关联TABLEA.A1=TABLEB.B1,TABLEA.A2=TABLEB.B2,TABLEA.A3=TABLEB.B3
但是有可能TABLEA.A2,TABLEA.A3,TABLEB.B2,TABLEB.B3不一定有值(只有TABLEA.A1,TABLEB.B1有值)
哪个组合有值那就对应的组合关联起来(比如TABLEA.A2,TABLEB.B2 都有值这TABLEA.A2=TABLEB.B2)
SELECT *
FROM tableA a
INNER JOIN tableB b
ON a.A1 = B.B1
AND (a.A2 IS NULL OR b.B2 IS NULL OR a.A2 = b.B2)
AND (a.A3 IS NULL OR b.B3 IS NULL OR a.A3 = b.B3)
SELECT *
FROM tableA a
LEFT JOIN tableB b
ON a.A1 = B.B1
AND (a.A2 IS NULL OR b.B2 IS NULL OR a.A2 = b.B2)
AND (a.A3 IS NULL OR b.B3 IS NULL OR a.A3 = b.B3)
select *
from TABLEA a
join TABLEB b on a.A1=b.B1 and (a.A2=b.B2 or a.A3=b.B3)
