Sqlite3多表查询-条件语句设置-（初次接触数据库，无法准确描述，直接上图了）

[marslycan]

现在有如下需求：
假设有表A,B,C....(在链接数据库时遍历获取具体表名及个数)，确定有共有列“SN”（SN这一列各个表可以取相同名也可以不同名，就看后续后续合并需求了，但是确实需要依据这一列合并各个表的），其余列都是独立属性
希望将所有表合并成一张表---如下图：

初次接触数据库，无法非常准确的描述需求，只能上图了~~
ps:合并之前我对各个表的SN进行了去重复的处理
select SN from A where SN in(select SN from A group by SN having count(SN )<2)

求各位大大不吝赐教，给个思路

[marslycan]

这个版好像人很少

[mingqing6364]

SELECT
	sn.SN,
	A.A_1,
	A.A_2,
	B.B_1,
	B.B_2,
	C.C_1,
	C.C_2,
	C.C_3
FROM
	(
	SELECT SN FROM A UNION
	SELECT SN FROM B UNION
	SELECT SN FROM C
	) sn
	LEFT JOIN A ON sn.SN = A.SN
	LEFT JOIN B ON sn.SN = B.SN
	LEFT JOIN C ON sn.SN = C.SN

+----+------+------+------+------+------+------+------+
| SN | A_1  | A_2  | B_1  | B_2  | C_1  | C_2  | C_3  |
+----+------+------+------+------+------+------+------+
| 0  | NULL | NULL | xxx  | xxx  | NULL | NULL | NULL |
| 1  | NULL | NULL | xxx  | xxx  | NULL | NULL | NULL |
| 2  | xxx  | xxx  | NULL | NULL | xxx  | xxx  | xxx  |
| 3  | xxx  | xxx  | NULL | NULL | xxx  | xxx  | xxx  |
| 4  | xxx  | xxx  | xxx  | xxx  | xxx  | xxx  | xxx  |
| 5  | xxx  | xxx  | NULL | NULL | xxx  | xxx  | xxx  |
| 6  | NULL | NULL | NULL | NULL | xxx  | xxx  | xxx  |
+----+------+------+------+------+------+------+------+
7 rows in set (0.02 sec)

[marslycan]

引用 2 楼 mingqing6364 的回复:

SELECT
	sn.SN,
	A.A_1,
	A.A_2,
	B.B_1,
	B.B_2,
	C.C_1,
	C.C_2,
	C.C_3
FROM
	(
	SELECT SN FROM A UNION
	SELECT SN FROM B UNION
	SELECT SN FROM C
	) sn
	LEFT JOIN A ON sn.SN = A.SN
	LEFT JOIN B ON sn.SN = B.SN
	LEFT JOIN C ON sn.SN = C.SN

+----+------+------+------+------+------+------+------+
| SN | A_1  | A_2  | B_1  | B_2  | C_1  | C_2  | C_3  |
+----+------+------+------+------+------+------+------+
| 0  | NULL | NULL | xxx  | xxx  | NULL | NULL | NULL |
| 1  | NULL | NULL | xxx  | xxx  | NULL | NULL | NULL |
| 2  | xxx  | xxx  | NULL | NULL | xxx  | xxx  | xxx  |
| 3  | xxx  | xxx  | NULL | NULL | xxx  | xxx  | xxx  |
| 4  | xxx  | xxx  | xxx  | xxx  | xxx  | xxx  | xxx  |
| 5  | xxx  | xxx  | NULL | NULL | xxx  | xxx  | xxx  |
| 6  | NULL | NULL | NULL | NULL | xxx  | xxx  | xxx  |
+----+------+------+------+------+------+------+------+
7 rows in set (0.02 sec)

谢谢大神指导

[marslycan]

引用 2 楼 mingqing6364 的回复:

SELECT
	sn.SN,
	A.A_1,
	A.A_2,
	B.B_1,
	B.B_2,
	C.C_1,
	C.C_2,
	C.C_3
FROM
	(
	SELECT SN FROM A UNION
	SELECT SN FROM B UNION
	SELECT SN FROM C
	) sn
	LEFT JOIN A ON sn.SN = A.SN
	LEFT JOIN B ON sn.SN = B.SN
	LEFT JOIN C ON sn.SN = C.SN

+----+------+------+------+------+------+------+------+
| SN | A_1  | A_2  | B_1  | B_2  | C_1  | C_2  | C_3  |
+----+------+------+------+------+------+------+------+
| 0  | NULL | NULL | xxx  | xxx  | NULL | NULL | NULL |
| 1  | NULL | NULL | xxx  | xxx  | NULL | NULL | NULL |
| 2  | xxx  | xxx  | NULL | NULL | xxx  | xxx  | xxx  |
| 3  | xxx  | xxx  | NULL | NULL | xxx  | xxx  | xxx  |
| 4  | xxx  | xxx  | xxx  | xxx  | xxx  | xxx  | xxx  |
| 5  | xxx  | xxx  | NULL | NULL | xxx  | xxx  | xxx  |
| 6  | NULL | NULL | NULL | NULL | xxx  | xxx  | xxx  |
+----+------+------+------+------+------+------+------+
7 rows in set (0.02 sec)

你好~请教下，现在我遇到问题是当有几万行（每个表）合并时，会卡死，这个有什么办法么~