mysql数据库-面试题之一

面试的一道数据库查询题：

答案：select teams.team_id,teams.team_name, coalesce(totals.num_points,0) as num_points

from teams left join (

select team_id,sum(points) as num_points from

(select host_team as team_id, 3 as points

from matches where host_goals > guest_goals

union

select guest_team as team_id, 3 as points

from matches where guest_goals > host_goals

union

select host_team as team_id, 1 as points

from  matches where host_goals = guest_goals

union

select guest_team as team_id, 1 as points

from matches where guest_goals = host_goals

) as results  

group by team_id  

) as totals

on teams.team_id = totals.team_id

order by num_points desc,team_id;

***********************************************************************************************************************************

或者 ：【这个大小写会清晰很多】

SELECT t.team_id, t.team_name, COALESCE(totals.num_points, 0) as num_points
    -> FROM teams t LEFT JOIN (
    ->     SELECT team_id, SUM(points) as num_points
    ->     from (
    ->     SELECT host_team as team_id, 3 as points
    ->     FROM matches where host_goals > guest_goals
    ->     UNION
    ->     SELECT guest_team as team_id, 3 as points
    ->     FROM matches where host_goals < guest_goals
    ->     UNION 
    ->     SELECT host_team as team_id, 1 as points
    ->     FROM matches where host_goals = guest_goals
    ->     UNION
    ->     SELECT guest_team as team_id, 1 as points
    ->     FROM matches where host_goals = guest_goals
    -> ) as results
    ->     GROUP BY team_id
    -> ) as totals
    -> ON t.team_id = totals.team_id 
    -> order by num_points desc, team_id asc;
 

用到的知识点：

1.coalesce函数，结果作为每一个球队的总分数，若不给参数0，则返回的结果是null

2.多表查询中的左连接 left join ...表名【这里派生表必须有一个别名】. on

3.   聚合函数sum

4. 子查询 语法：select * from 表1 inner join 表2 on 表1.列 运算符 表2.列 where 条件

5.分组：select c_gender from t_student group by c_gender;

6.排序：desc降序，asc升序，默认为升序

COALESCE是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。

UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。SQL UNION 语法，SELECT column_name(s) FROM table_name1

 

---

建数据库：

create database interview charset=utf8;

建表：

create table teams (team_id integer unique not null ,team_name varchar(30) not null);

 create table matches (match_id integer unique not null ,host_team integer not null,guest_team integer not null,host_goals integer not null,guest_goals integer not null);
 

插入数据:

insert into teams  values(10,'Give'),(20,'Never'),(30,'You'),(40,'Up'),(50,'Gonna');
insert into matches values(1,30,20,1,0),(2,10,20,1,2),(3,20,50,2,2),(4,10,30,1,0),(5,30,50,0,1);
 

创建主键、外键：

alter table teams add constraint PK_id primary key (team_id);
 alter table matches add constraint FK_host_team foreign key (host_team) references teams(team_id);
alter table matches add constraint FK_guest_team foreign key (guest_team) references teams(team_id);