比较两个Count()值sql

Question

我有一个名为" took "它包含以下信息。获取(stud_id, class_id, sec_id，学期，学年，年级)

我想找出2009年比2010年上课更多的学生的id(以课程数量计算)。我使用以下代码提取2009年学生的总班级数，但不确定如何进行比较

select stud_id, count(class_id) from Takes where year = 2009 group by stud_id;

Answer 1

您可以使用条件聚合(CASE WHEN在聚合函数中)和HAVING子句:

select
stud_id,
count(case when year = 2009 then 1 end) as classes_in_2009,
count(case when year = 2010 then 1 end) as classes_in_2010
from takes 
group by stud_id
having count(case when year = 2009 then 1 end) >
count(case when year = 2010 then 1 end);

Answer 2

这不是最有效的查询，但你可以试试:

SELECT aa.Stud_id
FROM (  SELECT Stud_id, Year, COUNT(Class_id) Class_id
FROM Takes
GROUP BY Stud_id, Year) aa
WHERE aa.Year = 2009
AND aa.Class_id > (SELECT bb.Class_id
FROM (  SELECT Stud_id, Year, COUNT(Class_id) Class_id
FROM Takes
GROUP BY Stud_id, Year) bb
WHERE aa.Stud_id = bb.Stud_id AND bb.Year = 2010);

通过立即应用WHERE子句并使用HAVING进行计数比较，可以提高效率:

SELECT aa.Stud_id
FROM Takes aa
WHERE aa.Year = 2009
GROUP BY aa.Stud_id
HAVING COUNT(*) > 
(
SELECT COUNT(*)
FROM Takes bb
WHERE bb.Stud_id = aa.Stud_id
AND bb.Year = 2010
);

Answer 3

根据dbms的不同，2009左连接2010可能会加快速度:

select t2009.stud_id, t2009.cnt, t2010.cnt
from
(select stud_id, count(*) as cnt
from takes
where year = 2009
group by stud_id) t2009
LEFT JOIN
(select stud_id, count(*) as cnt
from takes
where year = 2010
group by stud_id) t2010
ON t2009.stud_id = t2010.stud_id AND t2009.cnt > t2010.cnt

(假设年份列已被索引)