主表要两次与具有相同列的参考表连接两次。主表列是用整数插入的,需要与引用表连接才能取回字符以显示给用户。下面的sql没有显示任何记录,为什么?
手台 BD_BRAND
Sports_BR Leather_BR
2 1
参考表 BD_REF
ID NME REF_TYPE
1 NIKE Sports_Brand
2 ADIDAS Sports_Brand
3 PUMA Sports_Brand
1 CLACKS Leather_Brand
2 LOTTUSSE Leather_Brand
3 CHEANEY Leather_Brand
SELECT B.NME AS Sports_BR, C.NME AS Leather_BR
FROM BD_BRAND A
LEFT JOIN BD_REF B on B.ID = A.Sports_BR
LEFT JOIN BD_REF C on C.ID = A.Leather_BR
我想要的结果如下:
Sports_BR Leather_BR
ADIDAS CLACKS
我认为您正在寻找:
SELECT B.NME AS Sports_BR, C.NME AS Leather_BR
FROM BD_BRAND A
LEFT JOIN BD_REF B on B.ID = A.Sports_BR and B.REF_TYPE = 'Sports_Brand'
LEFT JOIN BD_REF C on C.ID = A.Leather_BR and B.REF_TYPE = 'Leather_Brand'
由于不同的引用类型,您的 ID 会多次出现,因此您需要根据引用类型指定要抓取的 ID。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句