mysql - count(*) over() MSSQL 到 Mysql

Question

我想将下面的 MSSQL 查询转换为 Mysql 查询，特别是使用 ROWNUMBER() 和 OVER()。

更新实际查询。

WITH interviewResults AS(
    Select ROW_NUMBER() OVER(ORDER BY min(T_Interview.ScheduleUtc) desc) as rownum,COUNT(*) over() as totalCount,T_Job.id as jobId,
        T_application.Id as applicationId,T_Job.Title as requisitionTitle,T_Candidate.FirstName as candidateFirstName,T_Candidate.LastName as candidateLastName
        ,T_Job.requisitionId as requisitionId,T_interview.InterviewId as interviewId,T_interview.GroupId as groupId
        ,min(T_Interview.ScheduleUtc) as interviewTime,cs2.Name as interviewTypeTitle,cs2.id as candidateStateId 
    from T_Application  with(nolock) 
    join T_Job on T_Job.Id = T_Application.JobId 
    join T_JobOwner with(nolock) on T_Jobowner.JobId = T_Job.Id 
    join T_Interview with(nolock) on T_Application.id=T_Interview.ApplicationId 
    join T_InterviewType with(nolock) on T_Interview.InterviewId = T_InterviewType.Id 
    join T_Candidate with(nolock) on T_Application.CandidateId =T_Candidate.Id 
    join T_CandidateState cs with(nolock) on cs.Id = T_Application.WorkflowState 
    join T_CandidateState cs2 with(nolock) on cs2.ItemId = T_InterviewType.Id 
    where T_Application.CompanyId= 153 and T_Application.Deleted = 0 and T_Application.DeletedHM=0 
        and T_Application.TrashHM is NULL and T_Job.Deleted=0 and T_Job.state & 1 != 0 
        and T_Interview.ScheduleUtc >= '2016-01-20 07:59:59' and T_Interview.ScheduleUtc <= '2017-01-16 07:59:59' and T_JobOwner.UserId=17003236 and ((cs.itemId not in (7, 5, 6) 
        and cs.kind=0) or (cs.kind != 0)) and T_Interview.Completed = 0 
        and (T_Interview.Flags & 32 = 0) and (T_Interview.Flags & 2 = 0) and cs2.CompanyId=153 
        and cs2.Deleted=0 and cs2.Kind=1 and T_Job.workflowId = cs2.workflowId 
    group by T_Interview.InterviewId,T_Interview.GroupId,T_Job.RequisitionId,
        T_Job.Title,T_Candidate.FirstName,T_Candidate.LastName,T_Application.Id,T_Job.id,cs2.Name,cs2.id)

    SELECT Rownum,totalCount,jobId,applicationId,requisitionTitle,candidateFirstName,candidateLastName,requisitionId,interviewId,
    groupId,interviewTime,interviewTypeTitle,candidateStateId 
    FROM interviewResults 
    WHERE RowNum BETWEEN 1 AND 5  

Answer 1

select *, count(T_App.JobId) as numJobs
from from T_App  
          join T_Jo on T_Jo.Id = T_App.JobId
          join (
                select @rn := @rn + 1 as rn, Appid, minScheduleUtc
                from 
                    (select @rn := 0) x,
                    (select Appid, min(T_Int.ScheduleUtc) minScheduleUtc
                     from T_Int
                     group by Appid
                     order by min(T_Int.ScheduleUtc) desc) y
               ) z on z.Appid = T_App.Appid
where rn between 1 and 5