在SQL Server中获得Max和count在一起
问题描述:
我想获得最大假期列和ID与最大假期相关......我如何调整查询?在SQL Server中获得Max和count在一起
select cal.schoolid,count(d.instruction) as holiday
from day d
inner join calendar cal on d.calendarID=cal.calendarID
inner join schoolyear sy on sy.endyear=cal.endyear and sy.active=1
where d.instruction=0
group by cal.schoolid
下面是结果:
ID Holidays
237 86
240 86
261 168
262 168
264 155
266 168
267 170
答
使用Top 1
得到Max Holiday
SELECT TOP 1 cal.schoolid,
Count(d.instruction) AS holiday
FROM [day] d
INNER JOIN calendar cal
ON d.calendarID = cal.calendarID
INNER JOIN schoolyear sy
ON sy.endyear = cal.endyear
AND sy.active = 1
WHERE d.instruction = 0
GROUP BY cal.schoolid
ORDER BY Holiday DESC
或者使用Window Function
;WITH cte
AS (SELECT cal.schoolid,
Count(d.instruction) AS holiday
FROM [day] d
INNER JOIN calendar cal
ON d.calendarID = cal.calendarID
INNER JOIN schoolyear sy
ON sy.endyear = cal.endyear
AND sy.active = 1
WHERE d.instruction = 0
GROUP BY cal.schoolid)
SELECT ID,
Holidays
FROM (SELECT Row_number()OVER (ORDER BY Holidays DESC) rn,*
FROM cte) A
WHERE rn = 1
答
结果莫名其妙地从你选择什么(我指的是列名)不同。总之,使用MAX()
:
select cal.schoolid,MAX(count(d.instruction)) as holiday
from day d
inner join calendar cal on d.calendarID=cal.calendarID
inner join schoolyear sy on sy.endyear=cal.endyear and sy.active=1
where d.instruction=0
group by cal.schoolid
+0
我不认为这项工作 – 2014-12-04 01:36:51
答
select schoolid, max(holiday) from(select cal.schoolid,count(d.instruction) as
holiday
from day d
inner join calendar cal on d.calendarID=cal.calendarID
inner join schoolyear sy on sy.endyear=cal.endyear and sy.active=1
where d.instruction=0
group by cal.schoolid) group by schoolid
非常感谢@ NOdisplayName ....这工作。 – hets 2014-12-04 15:39:31