usr_idlog_date0012021/5/10022021/5/10032021/5/10012021/5/20032021/5/20012021/5/30032021/5/4上面面表格是用户访问表 users记录了用户 idusr_id和访问日期log_date求出连续 3 天以上访问的用户 id。解题思路我们需要根据这么一个简单的表求出连续 3 天以上访问的用户。可以按照用户 id 给访问日期排名然后再用访问日期减去排名得到一个时间。如果用户是连续访问的这个时间就是一样的一个用户的这个时间如果出现 3 次及以上说明这个用户连续访问了 3 天。首先生成模拟数据CREATETABLEusers(usr_idVARCHAR(10)NOTNULLCOMMENT用户ID,log_dateDATENOTNULLCOMMENT访问日期)ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT用户访问记录表;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);第一步先按照用户 idusr_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 天所以我们对上面查出的这个结果进行分组并统计判断是否大于 3selectusr_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 部门信息表Employee 员工信息表字段工号 Id姓名 Name工资 Salary部门编号 DepartmentIdDepartment 部门信息表字段部门编号 ID部门名称 NameEmployee 员工表数据idnamesalarydepartment_id1Joe8500012Henry8000023San6000024Max9000015Janet6900016Randy8500017Will700001Department 部门表数据idname1IT2Sales查询需求编写一个 SQL 查询找出每个部门获得前三高工资的所有员工相同工资并列排名。预期输出结果表department_nameemployee_namesalaryITMax90000ITJoe85000ITRandy85000ITWill70000SalesHenry80000SalesSan60000建表语句CREATETABLEDepartment(idINTPRIMARYKEYCOMMENT部门编号,nameVARCHAR(20)NOTNULLCOMMENT部门名称)ENGINEInnoDBDEFAULTCHARSETutf8mb4;INSERTINTODepartment(id,name)VALUES(1,IT),(2,Sales);CREATETABLEEmployee(idINTPRIMARYKEYCOMMENT员工工号,nameVARCHAR(20)NOTNULLCOMMENT员工姓名,salaryINTNOTNULLCOMMENT工资,department_idINTCOMMENT部门编号,FOREIGNKEY(department_id)REFERENCESDepartment(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4;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.ide.department_id)temwheretem.salary_rank3## 或者selectt.nameasdepartment_name,e.name,e.salaryfromEmployee eleftjoinDepartment tont.ide.department_idwheree.idin(selectidfrom(selectid,name,salary,department_id,DENSE_RANK()over(PARTITIONbynameORDERBYsalarydesc)asrank_idfromEmployee)temwheretem.rank_id3)