Question

SQL求两张表如何合并。

如图，将左边两张表，合并成右边一张表的效果，请问语句怎么写？

Answer 1

先用左连接将1表完全查出,再用右连接将2表完全查出,最后合并结果

SELECT t1.id AS `编号`,t1.`name` AS `名称`,t1.score AS `科目一分数`,CASE WHEN t2.score IS NULL THEN 0 ELSE t2.score END AS `科目二分数`

FROM t1 LEFT JOIN t2 ON t1.id= t2.id

UNION

SELECT t2.id AS `编号`,t2.`name` AS `名称`,CASE WHEN t1.score IS NULL THEN 0 ELSE t1.score END AS `科目一分数`,t2.score AS `科目二分数`

FROM t1 RIGHT JOIN t2 ON t1.id= t2.id;

追问

不好意思，如果还有一张表是科目三的学分，三张表（或更多张表）要整合到一起怎么写？

追答

SELECT t.id,t.`name`,MAX(t.score1) AS `科目一成绩`,MAX(t.score2) AS `科目二成绩`,MAX(t.score3) AS `科目三成绩`
FROM (SELECT id,`name`,score AS `score1`,0 AS `score2`,0 AS `score3` FROM t1
UNION ALL
SELECT id,`name`,0 AS `score1`,score AS `score2`,0 AS `score3` FROM t2
UNION ALL
SELECT id,`name`,0 AS `score1`,0 AS `score2`,score AS `score3` FROM t3) t
GROUP BY t.id;

这个解法更好

Answer 2

呵呵。没有任何信息。就很难办了。应该找出用户的信息。以及是如何导出的。
或者strings 这个文件 看看有没有有用的信息