SQLite 多表查询重复记录 sql多表联合查询 去重

一，表的加法

表的加法是指通过关键字union将格式相同的表按照行合并在一起，合并后的表会自动去重；使用关键字union all 可保留重复行，将两个表去重合并语句如下：

select  <列名1>,<列名2>......
from  <表1>
union
select  <列名1>,<列名2>......
from  <表2>;

二，表的联结（以两张表的联结为例）

多张表之间通过共同的字段（列）进行关联和匹配，这种关系在数据库中叫"联结"。

表之间的联结方式有五种：交叉联结，内联结，左联结，右联结，全联结。

1.交叉联结（cross jion）又叫笛卡尔积，它是指一张表的每一行与令一张表的每一行合并在一起。交叉联结在实际中并不常用，它产生的结果行数太多，需要花费大量的运算成本和设备的支持，且结果没有实际价值。SQL不支持交叉联结。

2.内联结（inner join）是指查找出两张表共同字段所在的行。内联结的查询原理如下图所示

例如查询学号，姓名和课程号，SQL的查询过程：先分别从两张表中取出符合条件的行，再进行交叉联结，如图所示

SQL语句：

select a.学号, a.姓名, b.课程号
from student as a inner join score as b
on a.学号=b.学号;

3.左联结

左联结（left join）是指将左侧表的数据全部查询出来，从右表中取出两张表共同字段所在的行与之合并，左联结的查询原理如下图所示

例如，采用左联结的方式查询学号，姓名和课程号，SQL的查询过程和结果：

右表中没有匹配项的行以null值显示，添加”where b.学号=Null“条件字句可在上述查询的基础上去掉两张表共同的部分

SQl语句：

select a.学号, a.姓名, b.课程号
from student as a left join score as b
on a.学号=b.学号;

4.右联结

右联结（right join)正好与左联结相反，它是指将右侧表的数据全部查询出来，从左表中取出两张表共同字段所在的行与之合并。右联结的查询原理如下图所示

例如，采用右联结的方式查询学号，姓名和课程号，SQL的查询过程和结果：

左表中没有匹配项的行以null值显示，添加”where a.学号=Null“条件字句可在上述查询的基础上去掉两张表共同的部分

SQl语句：

select a.学号, a.姓名, b.课程号
from student as a right join score as b
on a.学号=b.学号;

5.全联结

全联结（full join）是指将两张表的数据全部取出，分别匹配相同字段所在的行。SQl也不支持全联结。

全联结的原理及查询过程以下两张图所示

三, case 表达式

case 表达式可以帮助我们解决复杂查询问题，case表达式相于一个判断函数，用来判断某一行是不是满足某个条件，如果满足条件就运行后面的then字句，case表达式运行结束；如果不符合条件，就进入下一个when字句，如果直到最后一个when字句也没有符合条件的数据，那么就会运行else后面的表达式。

else子句可以省略不写，如果省略不写，默认else返回空值；end不能省略；case可用于任何子句。

当有多种条件需要判断时，就可以使用case表达式。

case when <判断表达式>  then <表达式>
     when <判断表达式>  then <表达式>
     when <判断表达式>  then <表达式>
     ……
     else<表达式>
end

四，练习题

1.查询所有学生的学号,姓名，选课数，总成绩

select score.学号,student.姓名,count(score.课程号) as 选课数,sum(score.成绩) as 总成绩
from score right join student on score.学号=student.学号
group by score.学号;

2.查询平均成绩大于85的所有学生的学号，姓名和平均成绩

select score.学号,student.姓名,avg(score.成绩) as 平均成绩
from score right join student on score.学号=student.学号
group by score.学号
having avg(score.成绩)>85;

3.查询学生的选课情况：学号，姓名，课程号，课程名称

select student.学号,student.姓名,course.课程号,course.课程名称
       from student inner join score  on student.学号=score.学号 
                     inner join course on score.课程号=course.课程号;

4.查询每门课程的及格人数和不及格人数

select 课程号,
    sum(case when 成绩>=60 then 1 else 0 end) as 及格人数, 
    sum(case when 成绩<60 then 1 else 0 end) as 不及格人数
from  score
group by 课程号;

5.使用分段[100-85][85-70][70-60][<60]来统计各科成绩，分别查询各分数段人数，课程ID和课程名称

select  score.课程号,
        course.课程名称,
        sum(case when 成绩 between 85 and 100 then 1 else 0 end) as '[100-85]',
        sum(case when 成绩>=70 and 成绩<85  then 1 else 0 end) as '[85-70]',
        sum(case when 成绩>=60 and 成绩<70  then 1 else 0 end) as '[70-60]',
        sum(case when  成绩<60  then 1 else 0 end) as '[<60]'
from score right join course on  score.课程号=course.课程号
group by  score.课程号,course.课程名称;

6.Use the same JOIN as in the previous question.

Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'

select team1,team2,player
from goal inner join  game on id=matchid
where player like 'mario%';

7.The table eteam gives details of every national team including the coach. You can JOIN goalto eteam using the phrase goal JOIN eteam on teamid=id

Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10

SELECT goal.player, goal.teamid, eteam.coach, goal.gtime
  FROM  eteam  left join goal on teamid=id
 WHERE gtime<=10;

8.To JOIN game with eteam you could use eithergame JOIN eteam ON (team1=eteam.id) or game JOIN eteam ON (team2=eteam.id)

Notice that because id is a column name in both game and eteam you must specify eteam.id instead of just id

List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.

select  game.mdate, eteam.teamname
from eteam inner join game on eteam.id=team1
where  coach= 'Fernando Santos' ;

9.List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'

select goal.player
from goal inner join game on matchid=id
where stadium ='National Stadium, Warsaw';

10.The example query shows all goals scored in the Germany-Greece quarterfinal.

Instead show the name of all players who scored a goal against Germany.

HINT

Select goals scored only by non-German players in matches where GER was the id of either team1 or team2.
You can use teamid!='GER' to prevent listing German players.
You can use DISTINCT to stop players being listed twice.

SELECT distinct player
  FROM game lett JOIN goal ON matchid = id 
    WHERE (teamid=team2 and team1='GER') or (teamid=team1 and team2='GER');

11.Show teamname and the total number of goals scored.

SELECT teamname,count(teamid)
  FROM eteam JOIN goal ON id=teamid
  group BY teamname;

12.Show the stadium and the number of goals scored in each stadium.

SELECT stadium,count(stadium)
  FROM goal  inner join game ON id=matchid
  group BY stadium;

13.For every match involving 'POL', show the matchid, date and the number of goals scored.

select a.id, a.mdate, count(b.player)
from game as a inner join goal as b on a.id = b.matchid 
where (team1 = 'POL' OR team2 = 'POL')
group by a.id,a.mdate;

14.List every match with the goals scored by each team as shown. This will use "CASE WHEN" which has not been explained in any previous exercises.

mdate	team1	score1	team2	score2
1 July 2012	ESP	4	ITA	0
10 June 2012	ESP	1	ITA	1
10 June 2012	IRL	1	CRO	3
...

Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

SELECT mdate,team1,sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) as score1,team2, 
                   sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) as score2
 FROM game  left JOIN goal ON matchid = id
 group by  mdate,id,team1,team2;