SQL Server 常用统计查询语句实战汇总

基础统计：总分与平均分计算

第1步：计算个人总成绩并默认排序

目的：按姓名分组，汇总每位学生的所有科目得分，并按总分升序排列。此方法适用于简单的数据统计展示。

SELECT name, SUM(score) AS allscore 
FROM stuscore 
GROUP BY name 
ORDER BY allscore;

第2步：关联明细表的总成绩排名

目的：当需要同时显示学生详细信息（如学号）与总分，并进行降序排名时，需使用子查询或临时结果集进行自连接。此写法可确保结果集的完整性。

SELECT DISTINCT t1.name, t1.stuid, t2.allscore 
FROM stuscore t1, 
     (SELECT stuid, SUM(score) AS allscore FROM stuscore GROUP BY stuid) t2 
WHERE t1.stuid = t2.stuid 
ORDER BY t2.allscore DESC;

第3步：计算个人单科最高成绩

目的：找出每个学生在哪一科目取得了最高分，并返回该科目的具体成绩。通过内连接匹配最高分记录，避免重复数据。

SELECT t1.stuid, t1.name, t1.subject, t1.score 
FROM stuscore t1, 
     (SELECT stuid, MAX(score) AS maxscore FROM stuscore GROUP BY stuid) t2 
WHERE t1.stuid = t2.stuid AND t1.score = t2.maxscore;

第4步：统计每个人的平均成绩

目的：计算每位学生所有科目的算术平均值，常用于评估学生的整体学习水平。

SELECT DISTINCT t1.stuid, t1.name, t2.avgscore 
FROM stuscore t1, 
     (SELECT stuid, AVG(score) AS avgscore FROM stuscore GROUP BY stuid) t2 
WHERE t1.stuid = t2.stuid;

第8步：列出各门课程的平均成绩

目的：按学科维度统计平均分，用于对比不同课程的整体难度或教学效果。

SELECT subject, AVG(score) AS avgscore 
FROM stuscore 
GROUP BY subject;

高级排名与特定名次筛选

第5步：列出各门课程成绩最好的学生

目的：针对每门课程找出分数最高的学生记录。注意若存在并列第一，此基础写法可能因连接条件仅返回部分或全部最高分记录，视具体版本和索引而定。

SELECT t1.stuid, t1.name, t1.subject, t2.maxscore 
FROM stuscore t1, 
     (SELECT subject, MAX(score) AS maxscore FROM stuscore GROUP BY subject) t2 
WHERE t1.subject = t2.subject AND t1.score = t2.maxscore;

第6步：列出各门课程成绩最好的两位学生

目的：在每门课程中截取排名前两位的记录。利用相关子查询 `TOP 2` 实现动态排序限制，适合需要限定头部数据的场景。

SELECT DISTINCT t1.* 
FROM stuscore t1 
WHERE t1.id IN (
    SELECT TOP 2 id 
    FROM stuscore 
    WHERE subject = t1.subject 
    ORDER BY score DESC
)
ORDER BY t1.subject;

第9步：数学成绩的两种排名方式

目的：对比传统变量累加排名与窗口函数 `DENSE_RANK()` 的差异。前者模拟连续排名，后者支持并列排名且自动跳过后续名次。

方式一：使用变量模拟排名（兼容老版本）：

DECLARE @tmp TABLE(pm INT, name VARCHAR(50), score INT, stuid INT);
INSERT INTO @tmp 
SELECT NULL, name, score, stuid 
FROM stuscore 
WHERE subject='数学' 
ORDER BY score DESC;

DECLARE @id INT; SET @id=0;
UPDATE @tmp SET @id=@id+1, pm=@id;
SELECT * FROM @tmp;

方式二：使用 DENSE_RANK 窗口函数（推荐）：

SELECT DENSE_RANK() OVER(ORDER BY score DESC) AS row,
       name, subject, score, stuid 
FROM stuscore 
WHERE subject='数学' 
ORDER BY score DESC;

第10步：列出数学成绩在 2-3 名的学生

目的：截取特定排名区间的记录。通过多重嵌套 Top N 查询实现中间范围的过滤，需注意排序稳定性。

SELECT t3.* 
FROM (
    SELECT TOP 2 t2.* 
    FROM (
        SELECT TOP 3 name, subject, score, stuid 
        FROM stuscore 
        WHERE subject='数学' 
        ORDER BY score DESC
    ) t2 
    ORDER BY t2.score
) t3 
ORDER BY t3.score DESC;

第11步：求出特定学生（李四）的数学成绩排名

目的：定位指定用户在特定科目中的相对位置。结合变量赋值后直接筛选目标用户名即可得到其排名。

DECLARE @tmp TABLE(pm INT, name VARCHAR(50), score INT, stuid INT);
INSERT INTO @tmp 
SELECT NULL, name, score, stuid 
FROM stuscore 
WHERE subject='数学' 
ORDER BY score DESC;

DECLARE @id INT; SET @id=0;
UPDATE @tmp SET @id=@id+1, pm=@id;

-- 直接查询目标用户排名
SELECT * FROM @tmp WHERE name='李四';

复杂统计：分数段分布与行转列

第12步：课程不及格、良、优的人数统计

目的：将数值型成绩转换为分类统计，常用于生成数据透视类报表。利用子查询关联当前科目进行独立计数。

SELECT subject,
       (SELECT COUNT(*) FROM stuscore WHERE score<60 AND subject=t1.subject) AS 不及格,
       (SELECT COUNT(*) FROM stuscore WHERE score BETWEEN 60 AND 80 AND subject=t1.subject) AS 良,
       (SELECT COUNT(*) FROM stuscore WHERE score>80 AND subject=t1.subject) AS 优
FROM stuscore t1 
GROUP BY subject;

第7步：行转列展示各科成绩（透视表）

目的：将原本纵向存储的“科目-分数”格式转换为横向表格，直观展示每个学生的语文、数学、英语及总分。利用 `CASE WHEN` 配合聚合函数实现行列转换。

SELECT stuid AS 学号,
       name AS 姓名,
       SUM(CASE WHEN subject='语文' THEN score ELSE 0 END) AS 语文,
       SUM(CASE WHEN subject='数学' THEN score ELSE 0 END) AS 数学,
       SUM(CASE WHEN subject='英语' THEN score ELSE 0 END) AS 英语,
       SUM(score) AS 总分,
       SUM(score)/COUNT(*) AS 平均分
FROM stuscore
GROUP BY stuid, name
ORDER BY 总分 DESC;

第13步：字符串拼接输出同科多名成绩

目的：将多行文本合并为单一字符串字段，便于在应用层直接展示列表信息。利用变量累积法实现。

DECLARE @s VARCHAR(1000);
SET @s='';
SELECT @s = @s + ',' + name + '(' + CONVERT(VARCHAR(10), score) + '分)' 
FROM stuscore 
WHERE subject='数学';

SET @s=STUFF(@s, 1, 1, '');
PRINT '数学:'+@s;