两张表的关联问题

Question

本来以为很简单的问题，却做快一个小时了，没弄出来，惭愧。业务需要对LB01,LB02两张表以字段（SUPPLIERCD ,SUPPLIERNAME）关联，进行数据统计，其中SUPPLIERCD是非空字段，SUPPLIERNAME可以为空。我简单作了测试数据
LB01_PURRECEIVEBOOK （SUPPLIERCD ,SUPPLIERNAME）
XX01      (NULL)
XX01      XX01Name
XX01      XX01Only
LB02_UNPAIDBALANCE   （SUPPLIERCD ,SUPPLIERNAME）
XX01      (NULL)
XX01      XX01Name
两张表关联后，这样理论抽出数据应该为
LB01                            LB02
XX01      (NULL)              XX01      (NULL)
XX01      XX01Name            XX01      XX01Name
XX01      XX01Only           (NULL)     (NULL)可是：SELECT  LB01.SUPPLIERCD  ,LB01.SUPPLIERNAME  ,LB02.SUPPLIERCD  ,LB02.SUPPLIERNAME
FROM LB01_PURRECEIVEBOOK LB01 , LB02_UNPAIDBALANCE LB02
WHERE 
      LB01.SUPPLIERCD = LB02.SUPPLIERCD(+)
  AND LB01.SUPPLIERNAME = LB02.SUPPLIERNAME(+) 
  AND LB01.SUPPLIERCD = 'XX01'
 
SUPPLIERCD SUPPLIERNAME SUPPLIERCD_1 SUPPLIERNAME_1
XX01        XX01Name               XX01                     XX01Name
XX01        (NULL)                (NULL)                    (NULL) 
XX01        XX01Only              (NULL)                    (NULL) 
SELECT   LB01.SUPPLIERCD  ,LB01.SUPPLIERNAME  ,LB02.SUPPLIERCD  ,LB02.SUPPLIERNAME
FROM LB01_PURRECEIVEBOOK LB01 , LB02_UNPAIDBALANCE LB02
WHERE 
      LB01.SUPPLIERCD = LB02.SUPPLIERCD(+)
 AND   (( LB01.SUPPLIERNAME IS NULL AND LB02.SUPPLIERNAME IS NULL)
  OR    ( LB01.SUPPLIERNAME = LB02.SUPPLIERNAME)
       )
AND LB01.SUPPLIERCD = 'XX01'SUPPLIERCD SUPPLIERNAME SUPPLIERCD_1 SUPPLIERNAME_1
XX01        XX01Name               XX01                   XX01Name
XX01        (NULL)                 XX01                   (NULL) SELECT   LB01.SUPPLIERCD  ,LB01.SUPPLIERNAME  ,LB02.SUPPLIERCD  ,LB02.SUPPLIERNAME
FROM LB01_PURRECEIVEBOOK LB01 , LB02_UNPAIDBALANCE LB02
WHERE 
      LB01.SUPPLIERCD = LB02.SUPPLIERCD(+)
 AND   (( LB01.SUPPLIERNAME IS NULL AND LB02.SUPPLIERNAME IS NULL)
  OR    ( LB01.SUPPLIERNAME = LB02.SUPPLIERNAME)
  OR    ( LB01.SUPPLIERNAME IS NOT NULL AND LB02.SUPPLIERNAME IS NULL )
       )
AND LB01.SUPPLIERCD = 'XX01'SUPPLIERCD SUPPLIERNAME SUPPLIERCD_1 SUPPLIERNAME_1
XX01        XX01Name               XX01                   XX01Name
XX01        (NULL)                 XX01                   (NULL) 
XX01        XX01Only               XX01                   (NULL) 
XX01        XX01Name               XX01                   (NULL) 

Answer 1

兄弟，你可以常识使用LEFT JOIN 语法进行左连接的查询啊！~

Answer 2

SELECT  LB01.SUPPLIERCD  ,LB01.SUPPLIERNAME  ,LB02.SUPPLIERCD  ,LB02.SUPPLIERNAME
FROM LB01_PURRECEIVEBOOK LB01 , LB02_UNPAIDBALANCE LB02
WHERE 
      LB01.SUPPLIERCD = LB02.SUPPLIERCD
  AND LB01.SUPPLIERNAME = LB02.SUPPLIERNAME(+) 
  AND LB01.SUPPLIERCD = 'XX01'

Answer 3

我的第一个sql 就是left join 

Answer 4

这个如果数据是LB01（XX02,...），LB02中不存在XX02的对应数据就惨了

Answer 5

那就把左连接写成大家熟悉的形式吧。
SELECT   LB01.SUPPLIERCD  ,LB01.SUPPLIERNAME  ,LB02.SUPPLIERCD  ,LB02.SUPPLIERNAME
FROM LB01_PURRECEIVEBOOK LB01 LEFT JOIN LB02_UNPAIDBALANCE LB02
ON     LB01.SUPPLIERCD = LB02.SUPPLIERCD AND  LB01.SUPPLIERNAME = LB02.SUPPLIERNAME
WHERE LB01.SUPPLIERCD = 'XX01' SUPPLIERCD SUPPLIERNAME SUPPLIERCD_1 SUPPLIERNAME_1
XX01 XX01Name  XX01 XX01Name
XX01 XX01Only (NULL)    (NULL)
XX01 (NULL) (NULL) (NULL)

Answer 6

select a.suppliercd,nvl(a.suppliername,'0'),b.suppliercd,nvl(b.suppliername,'0')
from lb01 a,lb02 b
where a.suppliercd = b.suppliercd(+)
and nvl(a.suppliername,'0') = nvl(b.suppliername(+),'0');

Answer 7

因为suppliername存在空，所以判断相等的地方都要进行空处理，否则null=null不会成立的

Answer 8

select a.suppliercd,a.suppliername,b.suppliercd,b.suppliername
from LB01_PURRECEIVEBOOK a , LB02_UNPAIDBALANCE b
where a.suppliercd = b.suppliercd(+)
and nvl(a.suppliername,'0') = nvl(b.suppliername(+),'0')
and a.suppliercd = 'XX01'这样就对了

Answer 9

14:53:58 tina@PRACTICE> select * from LB01_PURRECEIVEBOOK;SUPPLIERCD SUPPLIERNAME
---------- ---------------
XX01       XX01Name
XX01       XX01Only
XX01已用时间:  00: 00: 00.01
14:54:11 tina@PRACTICE> select * from LB02_UNPAIDBALANCE;SUPPLIERCD SUPPLIERNAME
---------- ---------------
XX01
XX01       XX01Name已用时间:  00: 00: 00.00
14:54:21 tina@PRACTICE> SELECT LB01.SUPPLIERCD,LB01.SUPPLIERNAME,LB02.SUPPLIERCD SUPPLIERCD_1,LB02.SUPPLIERNAME SUPPLIERNAME_1
14:54:27   2  FROM LB01_PURRECEIVEBOOK LB01 , LB02_UNPAIDBALANCE LB02
14:54:27   3  WHERE LB01.SUPPLIERCD = LB02.SUPPLIERCD(+) AND NVL(LB01.SUPPLIERNAME,' ') = NVL(LB02.SUPPLIERNAME(+),' ')
14:54:27   4  AND LB01.SUPPLIERCD = 'XX01';SUPPLIERCD SUPPLIERNAME    SUPPLIERCD_1 SUPPLIERNAME_1
---------- --------------- ------------ ---------------
XX01                       XX01
XX01       XX01Name        XX01         XX01Name
XX01       XX01Only已用时间:  00: 00: 00.00

Answer 10

CREATE TABLE TMP_XQL_LB01_PURRECEIVEBOOK  AS 
SELECT 'XX01' SUPPLIERCD ,NULL SUPPLIERNAME    FROM DUAL
UNION ALL 
SELECT 'XX01','XX01Name'     FROM DUAL
UNION ALL 
SELECT 'XX01','XX01Only'     FROM DUAL
;CREATE TABLE TMP_XQL_LB02_UNPAIDBALANCE  AS 
SELECT 'XX01' SUPPLIERCD ,NULL SUPPLIERNAME    FROM DUAL
UNION ALL 
SELECT 'XX01','XX01Name'     FROM DUAL
;
SELECT * FROM  TMP_XQL_LB01_PURRECEIVEBOOK A,TMP_XQL_LB02_UNPAIDBALANCE B
WHERE A.SUPPLIERCD=B.SUPPLIERCD(+)
AND NVL(A.SUPPLIERNAME,'空值啊')= NVL(B.SUPPLIERNAME(+),'空值啊')

Answer 11

谢谢楼上的几位
为了防止隐患，
数据库SUPPLIERNAME定义为50位的可变字符串
我使用了
 AND NVL(LB01.SUPPLIERNAME,'S23456789012345678901234567890123456789012345678901') = NVL(LB02.SUPPLIERNAME(+),'S23456789012345678901234567890123456789012345678901') 

Answer 12

楼主理解NVL函数的作用了吗？。。
NVL(SUPPLIERNAME,val)当SUPPLIERNAME为NULL的时候才会取后面val的值,为了就是实现NULL=NULL的效果。。val只要是非空值就可以的

Answer 13

我是这样想的
你把NULL化为val的值然后进行比较
隐患就在于如果原来的数据就存在SUPPLIERNAME为val的元组，该怎么办？
比如数据库中原来就存在
('XX01','0')
('XX01',' ')
('XX01','空值啊')
这种数据虽然在实际数据中存在的可能性极小，但是一旦存在结果就不对了