我有 2 个表。

一张表定义了客户关系:

    CREATE TABLE IF NOT EXISTS `cust_connections` (
    `id` int(11) NOT NULL,
      `short_name` char(15) COLLATE utf8_unicode_ci NOT NULL,
      `source_fnn` char(10) COLLATE utf8_unicode_ci NOT NULL,
      `dest_fnn` char(10) COLLATE utf8_unicode_ci NOT NULL,
      `service_type` char(32) COLLATE utf8_unicode_ci NOT NULL,
      `ladder_side` char(10) COLLATE utf8_unicode_ci NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    INSERT INTO `cust_connections` (`id`, `short_name`, `source_fnn`, `dest_fnn`, `service_type`, `ladder_side`) VALUES
    (1, 'cust1', 'N2843453A', '', 'HD_300_Connect', 'src only'),
    (2, 'cust2', '', 'N2843600A', 'HD_300_Connect', 'dest only'),
    (3, 'cust3', 'N2720257O', 'N2731164O', 'DVB25_188byte', 'both'),
    (4, 'cust4', 'N27xxx7O', 'N2731164O', 'DVB25_188byte', 'src ukn'),
    (5, 'cust4', 'N27xxx7O', '', 'DVB25_188byte', 'ukn +blk');

ALTER TABLE `cust_connections`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `cust_connections`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=18;

另外一个表定义了设备:

     CREATE TABLE IF NOT EXISTS `cust_port` (
    `id` smallint(11) NOT NULL,
      `system_name` char(32) COLLATE utf8_unicode_ci NOT NULL,
      `slot_no` char(2) COLLATE utf8_unicode_ci NOT NULL,
      `port_no` char(2) COLLATE utf8_unicode_ci NOT NULL,
      `port_fnn` char(9) COLLATE utf8_unicode_ci NOT NULL
    ) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    INSERT INTO `cust_port` (`id`, `system_name`, `slot_no`, `port_no`, `port_fnn`) VALUES
    (1, '01-06C2:source', '7', '1', 'N2843453A'),
    (2, '01-27B4:dest', '1', '2', 'N2843600A'),
    (3, '01-27B6:source+dst', '17', '3', 'N2720257O'),
    (4, '01-27B6:dst+src', '17', '3', 'N2731164O'),
    (5, '01-32C6:dup_fnn1', '1', '2', 'N2845070O'),
    (26, '01-32C6:dup_fnn2', '1', '3', 'N2845070O'),
    (27, '01-32D6:no_fnn', '1', '4', ''),
    (28, '01-32D6:diff_fnn', '1', '4', 'x123456');

ALTER TABLE `cust_port`
 ADD PRIMARY KEY (`id`);

ALTER TABLE `cust_port`
MODIFY `id` smallint(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=29;

SQL 结果是:

 cc_id  short_name  source_fnn  dest_fnn    service_type    ladder_side     src_system_name     src_slot_no     src_port_no     src_port_fnn    dst_system_name     dst_slot_no     dst_port_no     dst_port_fnn    
1       cust1       N2843453A               HD_300_Connect  src only       01-06C2:source         7                  1          N2843453A       01-32D6:no_fnn       1              4   
2       cust2       N2843600A               HD_300_Connect  dest only      01-32D6:no_fnn         1                  4                          01-27B4:dest         1              2               N2843600A
3       cust3       N2720257O   N2731164O   DVB25_188byte   both           01-27B6:source+dst    17                  3          N2720257O       01-27B6:dst+src      17             3               N2731164O
4       cust4       N27xxx7O    N2731164O   DVB25_188byte   src ukn        NULL                   NULL               NULL       NULL            01-27B6:dst+src      17             3               N2731164O
5       cust4       N27xxx7O                DVB25_188byte   ukn +blk       NULL                   NULL               NULL       NULL            01-32D6:no_fnn       1              4

我正在对两个表进行连接。

问题是，如果 port_fnn 为空，我想排除该行，但如果任何客户源或目标 fnn 为空，我想显示该行。

我正在执行左(自)连接以匹配源和目标 fnn 与设备 fnn。不幸的是，我的客户 fnns 必须能够有空值。

如果设备表中没有空值，我的查询就很好用。 我的查询是:

SELECT 
cc.id AS cc_id, short_name,source_fnn, dest_fnn, service_type,ladder_side,
src.system_name AS src_system_name,
src.slot_no AS src_slot_no,
src.port_no AS src_port_no,
src.port_fnn AS src_port_fnn,
dst.system_name AS dst_system_name,
dst.slot_no AS dst_slot_no, 
dst.port_no AS dst_port_no,
dst.port_fnn AS dst_port_fnn
FROM cust_connections cc 
 LEFT JOIN cust_port src on cc.source_fnn=src.port_fnn 
 LEFT JOIN cust_port dst on cc.dest_fnn=dst.port_fnn 

在我的结果集中: 第 1 行 - 只有源 fnn。我希望目标字段的结果为空，即:

cc_id   short_name  source_fnn  dest_fnn    service_type    ladder_side     src_system_name     src_slot_no     src_port_no     src_port_fnn    dst_system_name     dst_slot_no     dst_port_no     dst_port_fnn    
1       cust1       N2843453A               HD_300_Connect  src only       01-06C2:source         7                  1          N2843453A       NULL                NULL             NULL            NULL

查询正在检测一个空 fnn 并填充没有关联 fnn 的设备。即:01-32D6:no_fnn。

第 2 行的 source_system_name 和第 5 行的 dst_system_name 会出现同样的问题。

最佳答案

''(空字符串)不为 NULL。 (哪个 sqlfiddle 输出为“(null)”。)

在文本中，不要像您最初在问题中那样写“NULL”或“”或“null”或“(null)”来指代空字符串。清楚什么是 '' 什么是 NULL。

'' = '' 但 NULL <> NULL。因此，当相等性测试涉及 '' 时，您的 LEFT JOIN 会在 cc.source_fnn 列和 src.port_fnn 列之间以及 cc.dest_fnn 列和 cust_port port_fnn 列之间找到匹配项。但是您不希望 LEFT JOIN 匹配这些行。

你可以这样说:

1. 声明所有 _fnn 列可为空，即声明为 NULL(默认值)而不是 NOT NULL，并在您现在使用 ''(空字符串)的表中使用 NULL。那么您的查询将给出正确的答案!

2. 需要 port_fnn <> '':

   FROM cust_connections cc
   LEFT JOIN cust_port src
   ON cc.source_fnn=src.port_fnn AND cc.source_fnn <> ''
   LEFT JOIN cust_port  dst
   ON cc.dest_fnn=dst.port_fnn AND cc.dest_fnn <> '';
   

3. 在 LEFT JOIN 之前从 cust_port 中删除这些行:

   FROM cust_connections cc
   LEFT JOIN
       (SELECT * FROM cust_port WHERE port_fnn <> ''
       ) src
   ON cc.source_fnn=src.port_fnn
   LEFT JOIN
       (SELECT * FROM cust_port WHERE port_fnn <> ''
       ) dst
   ON cc.dest_fnn=dst.port_fnn;
   

SQLfiddle for 1 using NULL和 for 2 & 3 using '' .这些添加了第二个 cust_port 行，其中缺少 port_fnn 以表明以上给出了正确的结果。您与 '' 一起使用的查询错误地从中生成了其他虚假行。

如果您希望 LEFT JOIN 中的 NULL 在输出中显示为空字符串，那么您可以对这些列使用 IFNULL:

IFNULL(dst.port_fnn,'') AS dst_port_fnn

关于php - self 加入并仅在一个表/一侧忽略空值，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/28060642/