多表联查:由内向外一层层的查询
先把其中一个表想要的数据查出来,并把查询结果命名为一张新表,然后外层查询这张新表与别的表(内连接等)
例子:
表1:tb_score_record
先将这张表中每个人score求和:
使用group by 按照stu_id分组,就Sum(score):
SELECT
stu_id,
SUM( score ) AS score
FROM
`tb_score_record`
GROUP BY
stu_id
结果:求出每个人的总分,但是这张表中只有stu_id,要拿着stu_id去第2张表中查对应的学生name
表2:tb_student
涉及到将多表联查,可以将第一个查询结果作为一张新表3,然后查询表3中stu_id=表2中id(学生id)的学生name。
SELECT
stuScore.stu_id,
stuScore.score,
stu.`name`
FROM
( SELECT stu_id, SUM( score ) AS score FROM `tb_score_record` GROUP BY stu_id ) AS stuScore
INNER JOIN
tb_student AS stu
ON stu.id = stuScore.stu_id
三表联查也一样,把第一个表的结果作为新的表与表2 进行联查,然后把联查结果作为新表与表3进行联查:
SELECT
stuInfo.stu_id,
stuInfo.score,
stuInfo.`name` ,
stuClass.class_id
FROM
(SELECT
stuScore.stu_id,
stuScore.score,
stu.`name`
FROM
( SELECT stu_id, SUM( score ) AS score FROM `tb_score_record` GROUP BY stu_id ) AS stuScore
INNER JOIN tb_student AS stu
ON stu.id = stuScore.stu_id) as stuInfo
INNER JOIN
`tb_class_stu` AS stuClass
ON stuInfo.stu_id=stuClass.stu_id