企业官网建设流程全解析

上面面表格是用户访问表 users，记录了用户 id（usr_id）和访问日期（log_date），求出连续 3 天以上访问的用户 id。

解题思路

我们需要根据这么一个简单的表，求出连续 3 天以上访问的用户。可以按照用户 id 给访问日期排名，然后再用访问日期减去排名，得到一个时间。如果用户是连续访问的，这个时间就是一样的，一个用户的这个时间如果出现 3 次及以上，说明这个用户连续访问了 3 天。

首先生成模拟数据

CREATETABLEusers(usr_idVARCHAR(10)NOTNULLCOMMENT'用户ID',log_dateDATENOTNULLCOMMENT'访问日期')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'用户访问记录表';INSERTINTOusers(usr_id,log_date)VALUES('001','2021-05-01'),('002','2021-05-01'),('003','2021-05-01'),('001','2021-05-02'),('003','2021-05-02'),('001','2021-05-03'),('003','2021-05-04');

第一步：
先按照用户 id（usr_id）对访问日期（log_date）进行排名，这里要用到 DENSE_RANK () 这个窗口函数，用于给出排名序号。这个函数经常应用在给学生成绩进行排名。
第二张图文字

selectusr_id,log_date,DENSE_RANK()OVER(PARTITIONBYusr_idorderbylog_date)ASrank_idfromusers

第二步：
得到排名后，我们用访问日期减去排名，得到一个时间 flg_date。

selectusr_id,DATE_SUB(log_date,INTERVALrank_idDAY)asflag_datefrom(selectusr_id,log_date,DENSE_RANK()OVER(PARTITIONBYusr_idorderbylog_date)ASrank_idfromusers)asA;

第三步：
同一个用户有 3 个及以上 flg_date 相同，说明用户连续访问了 3 天，所以我们对上面查出的这个结果进行分组，并统计判断是否大于 3

selectusr_id,DATE_SUB(log_date,INTERVALrank_idDAY)asflag_datefrom(selectusr_id,log_date,DENSE_RANK()OVER(PARTITIONBYusr_idorderbylog_date)ASrank_idfromusers)asAgroupbyusr_id,flag_datehavingcount(flag_date)>=3;

练习题目：找出每个部门工资前三高的员工（相同工资并列排名）

题目描述

现有两张数据表：Employee 员工信息表、Department 部门信息表

1. Employee 员工信息表字段：工号 Id，姓名 Name，工资 Salary，部门编号 DepartmentId
2. Department 部门信息表字段：部门编号 ID，部门名称 Name

Employee 员工表数据

Department 部门表数据

查询需求

编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工，相同工资并列排名。

预期输出结果表

建表语句：

CREATETABLEDepartment(idINTPRIMARYKEYCOMMENT'部门编号',nameVARCHAR(20)NOTNULLCOMMENT'部门名称')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;INSERTINTODepartment(id,name)VALUES(1,'IT'),(2,'Sales');CREATETABLEEmployee(idINTPRIMARYKEYCOMMENT'员工工号',nameVARCHAR(20)NOTNULLCOMMENT'员工姓名',salaryINTNOTNULLCOMMENT'工资',department_idINTCOMMENT'部门编号',FOREIGNKEY(department_id)REFERENCESDepartment(id))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;INSERTINTOEmployee(id,name,salary,department_id)VALUES(1,'Joe',85000,1),(2,'Henry',80000,2),(3,'San',60000,2),(4,'Max',90000,1),(5,'Janet',69000,1),(6,'Randy',85000,1),(7,'Will',70000,1);

答案：

selecttem.department_name,tem.employee_name,tem.salaryfrom(selectd.nameasdepartment_name,e.nameasemployee_name,salary,department_id,DENSE_RANK()over(PARTITIONbye.nameORDERBYsalarydesc)assalary_rankfromEmployee eleftjoinDepartment dond.id=e.department_id)temwheretem.salary_rank<=3## 或者selectt.`name`asdepartment_name,e.name,e.salaryfromEmployee eleftjoinDepartment tont.id=e.department_idwheree.idin(selectidfrom(selectid,name,salary,department_id,DENSE_RANK()over(PARTITIONbynameORDERBYsalarydesc)asrank_idfromEmployee)temwheretem.rank_id<=3)