mysql联立两个表_SQL例题收集（代码运行于MySQL）

第一题：获取拥有最新登陆时间的设备ID

Activity表如下：图-1

需要获取的数据为player首次登陆设备的device_id，得到结果如下：图-2

代码如下：

select player_id , device_id from

(select row_number()over(partition by player_id order by event_date) rowid , activity.* from activity) as A1

where rowid=1;

使用到的函数：

row_number()、over()、partition by、order by

开窗函数：

OVER(PARTITION BY)函数

开窗函数，Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：分析函数对于每个组返回多行，而聚合函数对于每个组只返回一行。

over函数的写法：

上题中 over(partition by player_id order by event_date)

按照event_date排序进行累计，order by是个默认的开窗函数，按照player_id分区。

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。(可使用下面的代码建立T2_TEMP表，以下解释基于该表)SQL 例题表代码(第一题)​www.jianshu.com

开窗的窗口范围：

over(order by sroce range between 5 preceding and 5 following)：窗口范围为当前行数据幅度减5加5后的范围内的。

over(order by sroce rows between 5 preceding and 5 following)：窗口范围为当前行前后各移动5行。OVER(PARTITION BY)函数介绍​www.cnblogs.com

第二题：找出各分组中某属性最大的值

project表：

employee表：

查询每个项目中经验最丰富的(employee_years最大的)员工，结果如下：

代码如下：

select t.project_id,t.employee_id from(

select p.project_id,p.employee_id,rank()over(partition by p.project_id order by experience_years desc) qa from project p

left outer join employee e

on p.employee_id=e.employee_id) t

where qa=1;

使用的依然是开窗函数，相较于第一题，第二题是两表联立，需要使用到JOIN。

两题中使用的row_number()和rank()函数的差别在于rownumber()只返回一个结果，比如有两人并列第一则只会返回一个结果，根据employee表可以看出experience_years中有相同经验的员工，因此这里使用rank()。

待续……