代码之家 › 专栏 › 技术社区 › BlackICE

MySQL左外连接where子句

outer-join sql mysql

BlackICE · 技术社区 · 7 年前

我正在尝试使用以下MySQL查询:

SELECT *
FROM top_lines t
LEFT OUTER JOIN last_24_topline AS l ON l.`member_no` = t.`member_no` 
    AND l.`mfg` = t.`line_no`
WHERE l.account_no = 32049 OR l.account_no IS NULL

仅供参考,此查询按预期工作:

SELECT *
FROM top_lines t
LEFT OUTER JOIN last_24_topline l ON l.`member_no` = t.`member_no` 
    AND l.`mfg` = t.`line_no`
    AND l.`account_no` = 32049

CREATE TABLE `last_24_topline` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `member_no` varchar(30) NOT NULL,
  `branch_no` int(11) DEFAULT NULL,
  `employee_no` varchar(25) DEFAULT NULL,
  `account_no` varchar(25) DEFAULT NULL,
  `salesperson_name` varchar(255) DEFAULT NULL,
  `customer_name` varchar(255) DEFAULT NULL,
  `mfg` varchar(5) DEFAULT NULL,
  `mfg_description` varchar(255) DEFAULT NULL,
  `last_three` decimal(10,2) DEFAULT '0.00',
  `last_twelve` decimal(10,2) DEFAULT '0.00',
  `ly_last_three` decimal(10,2) DEFAULT '0.00',
  `ly_last_twelve` decimal(10,2) DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `ix_branch_no` (`branch_no`),
  KEY `ix_employee_no` (`employee_no`),
  KEY `ix_member_line_account` (`member_no`,`mfg`,`account_no`),
  KEY `ix_member_line` (`member_no`,`mfg`),
  KEY `ix_account_no` (`account_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `top_lines` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_no` varchar(30) NOT NULL,
  `line_no` varchar(5) NOT NULL,
  `line_description` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_line_no` (`member_no`,`line_no`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1

insert  into `top_lines`(`id`,`member_no`,`line_no`,`line_description`) values (1,'520','772','FED ROTOR/DRUM');
insert  into `top_lines`(`id`,`member_no`,`line_no`,`line_description`) values (2,'520','952','FED SST CERAMIC');
insert  into `top_lines`(`id`,`member_no`,`line_no`,`line_description`) values (3,'520','954','FED SST FRICTION');
insert  into `top_lines`(`id`,`member_no`,`line_no`,`line_description`) values (4,'520','162','EVS FRICTION');

INSERT INTO `last_24_topline` (`id`, `member_no`, `branch_no`, `employee_no`, `account_no`, `salesperson_name`, `customer_name`, `mfg`, `mfg_description`, `last_three`, `last_twelve`, `ly_last_three`, `ly_last_twelve`) VALUES('1','520','0','10856','463854','FORD, JAMES,','JIFFY LUBE','459','FEDERATED AIR FILTER','0.00','15.21','0.00','0.00');
INSERT INTO `last_24_topline` (`id`, `member_no`, `branch_no`, `employee_no`, `account_no`, `salesperson_name`, `customer_name`, `mfg`, `mfg_description`, `last_three`, `last_twelve`, `ly_last_three`, `ly_last_twelve`) VALUES('2','520','0','10856','463854','FORD, JAMES,','JIFFY LUBE','460','FILTERS','0.00','0.00','0.00','16.48');
INSERT INTO `last_24_topline` (`id`, `member_no`, `branch_no`, `employee_no`, `account_no`, `salesperson_name`, `customer_name`, `mfg`, `mfg_description`, `last_three`, `last_twelve`, `ly_last_three`, `ly_last_twelve`) VALUES('3','520','0','10856','463854','FORD, JAMES,','JIFFY LUBE','863','SMP T SERIES','0.00','0.00','0.00','50.67');

我希望,即使最后一个\u24\u顶行中没有匹配的数据,第一个查询也会生成一个结果集,其中包含顶行中的所有行,最后一个\u24\u顶行中的列的值为null。

预期结果:

更新将所有数据复制到新表中会导致问题再次出现。我正试图把问题缩小到最低限度。

3 回复 | 直到 7 年前

Adrian Maxwell 7 年前

使用联接中涉及的列,而不是 l.account_no

SELECT *
FROM top_lines t
LEFT OUTER JOIN last_24_topline AS l ON l.`member_no` = t.`member_no`
       AND l.`mfg` = t.`line_no`
WHERE l.account_no = 32049 OR l.`member_no` IS NULL

SELECT *
FROM top_lines t
LEFT OUTER JOIN last_24_topline AS l ON l.`member_no` = t.`member_no`
       AND l.`mfg` = t.`line_no`
       AND l.account_no = 32049

Shidersz 7 年前

也尝试检测空字符串,可能字段不是空的,而是空字符串。

SELECT *
FROM
    top_lines t
LEFT JOIN
    last_24_topline AS l ON l.member_no = t.member_no AND l.mfg = t.line_no
WHERE
    (l.account_no = '' OR l.account_no = '32049' OR l.account_no IS NULL)

SELECT *
FROM
    top_lines t
LEFT JOIN
    last_24_topline AS l ON l.member_no = t.member_no AND l.mfg = t.line_no
WHERE
    l.id IS NULL
OR
    (l.id IS NOT NULL AND l.account_no = '32049')

Community Mohan Dere 6 年前

在联接中没有帐号的查询上,联接匹配上一个\u 24 \u toplines表中的行,但是匹配where子句中的帐号,这样它就被过滤掉了,不会被视为顶部行中的一行,而不会被视为最后一行中的匹配行。

将匹配最后一行的\u 24 \u顶行

WHERE l.account_no = 32049 OR l.account_no IS NULL

在联接中带有检查帐户\u no的查询仍将与顶行中的行匹配,但是不会从最后一个\u 24\u顶行中有一个匹配的行,这样您将得到一个包含顶行数据的行,并且最后一个\u 24\u顶行为空。