MySQL实现全连接示例（求两表并集）

MySQL实现全连接示例（求两表并集）

很简单的东西，终于悟出来了 ^ - ^

1. 准备数据

CREATE TABLE `lysl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `beijian` varchar(255) DEFAULT NULL,
  `ren` varchar(255) DEFAULT NULL,
  `lysl` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `xhsl` (
  `beijian` varchar(255) NOT NULL,
  `ren` varchar(255) NOT NULL,
  `xhsl` int(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT into lysl(beijian, ren, lysl) values('beijian1', 'ren1', 10);
INSERT into lysl(beijian, ren, lysl) values('beijian2', 'ren2', 20);

INSERT into xhsl(beijian, ren, xhsl) values('beijian3', 'ren1', 30);
INSERT into xhsl(beijian, ren, xhsl) values('beijian4', 'ren2', 40);

2.实现sql：

select
	beijian, ren, sum(lysl), sum(xhsl) 
from 
	(
		SELECT lysl.beijian, lysl.ren, lysl.lysl, null xhsl from lysl 
		union 
		select xhsl.beijian, xhsl.ren, null lysl,xhsl.xhsl from xhsl
	) t
group by beijian, ren

这里分组求和，是因为有可能存在两个表都有的数据，出现重复

3.结果