确定具有最高最大计数的会话 [英] Determining the session with the highest max count

问题描述

我有一个包含以下字段的表格：

I have a table that has the following fields:

• Visit_ID


• CPCCID
• 
  
• 日期


• Time_IN


• 超时


• li>
  
• LA_CPCCID

• Visit_ID
• CPCCID
• Date
• Time_IN
• Time-Out
• Course
• LA_CPCCID

共有3个工作阶段，分别为9-12,12-3和3-6。

There are 3 sessions, 9-12, 12-3 and 3-6.

我需要一个脚本来计算哪个会话具有最多的访问者。

I need a script that will calculate which session has the most visitors.

将确定会话＃和最大计数：

I have this attached code that will determine the session # and the max count:

select Time_In ,
CASE
When cast(Time_In as time) >'12:00:00' and cast(Time_In as time) <='15:00:00' /* and date = cast(GETDATE() as date)*/ then 'Session 2' 

when cast(Time_In as time) >'3:00:00' and cast(Time_In as time)<= '6:00:00' /*and date = cast(GETDATE() as date)*/ then 'Session 3'

else 'Session 1'
end "sessions"
from Lab_Visits2;


select max(visit.cnt) 
from
(select course, count(course) cnt
from Lab_Visits2
group by Course) visit;

推荐答案

如果我正确理解您的问题，返回会话和课程，按会话/课程分组的访问者最多。如果是这样，则使用两个 common-table-expressions 选择会话，然后按会话和课程分组以获取访问者数。最后，使用 row_number 建立最大值：

If I'm understanding your question correctly, you want to return the session and course with the most visitors grouped by session/course. If so, this uses a couple of common-table-expressions to select the sessions, and then groups by session and course to get the count of visitors. Finally, using row_number to establish the max:

with cte as (
  select 
      case
        when cast(Time_In as time) >'12:00:00' and cast(Time_In as time) <='15:00:00' /* and date = cast(GETDATE() as date)*/ then 'Session 2' 
        when cast(Time_In as time) >'3:00:00' and cast(Time_In as time)<= '6:00:00' /*and date = cast(GETDATE() as date)*/ then 'Session 3'
        else 'Session 1'
      end session,
      course
    from Lab_Visits2
  ), ctecnt as (
  select session, course, count(*) cnt
  from cte
  group by session, course
)
select session, course, cnt
from (
  select session, course, cnt, row_number() over (order by cnt desc) rn
  from ctecnt
  ) t
where rn = 1

• SQL Fiddle Demo


• SQL Fiddle Demo

如果我误解了只需要最高计数的会话（不管当然），然后只需从所有查询中删除课程字段。

If I misunderstood and you only want the session with the highest count (regardless of course), then just remove the course field from all of the queries.

这篇关于确定具有最高最大计数的会话的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！