确定最大计数最高的会话

Question

我有一张表，其中有以下字段：

• Visit_ID
• CPCCID
• 日期
• Time_IN
• 超时
• 课程
• LA_CPCCID

有3场会议，9-12场，12-3场和3-6场.

我需要一个脚本，将计算哪个会议有最多的访客。

我有以下附加代码，它将确定会话#和最大计数：

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;

Answer 1

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

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演示

如果我误解了，而且您只想要最高计数的会话(当然不管是什么)，那么只需从所有查询中删除“课程”字段即可。