比较两个Count()值sql

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

坚韧的跑步鞋 · Stata_Blogs/Stata新命令-p ...· 4 月前 ·

呐喊的猴子 · 牛佬和邱瑞新这两个罕见算不算是港漫里画风最差 ...· 5 月前 ·

无邪的楼房 · Apache Shiro ...· 5 月前 ·

开朗的枕头 · CorelDRAW2021版本下载 ...· 6 月前 ·

好帅的苦咖啡 · 建设更高水平的平安上海、法治上海，陈吉宁在市 ...· 7 月前 ·

腾讯云

开发者社区

文档意见反馈控制台

首页 TVP

文章/答案/技术大牛

发布

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

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);

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
);

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);

select t2009.stud_id, t2009.cnt, t2010.cnt
 (select stud_id, count(*) as cnt
  from takes
  where year = 2009
  group by stud_id) t2009

推荐文章

坚韧的跑步鞋 · Stata_Blogs/Stata新命令-pdslasso--众多控制变量和工具变量如何挑选？.md at master · arlionn/Stata_Blogs · GitHub

4 月前

呐喊的猴子 · 牛佬和邱瑞新这两个罕见算不算是港漫里画风最差的两位？【龙虎门吧】_百度贴吧

5 月前

无邪的楼房 · Apache Shiro Authorization | Apache Shiro

5 月前

开朗的枕头 · CorelDRAW2021版本下载百度网盘_coreldraw下载百度网盘-CSDN博客

6 月前

好帅的苦咖啡 · 建设更高水平的平安上海、法治上海，陈吉宁在市委政法工作会议上划重点

7 月前

3 个回答