萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 2008 聚合函數實戰教學:SUM/COUNT/AVG/MAX/MIN 與 GROUP BY、DISTINCT 進階用法

測試數據準備

CREATE TABLE TB_Students (ID INT IDENTITY PRIMARY KEY, Name NVARCHAR(16), Age INT);
INSERT INTO TB_Students(Name,Age) VALUES('Tiana',15),('Yardi',20),('ZhangYun',18);

CREATE TABLE TB_Class (ClassID INT IDENTITY PRIMARY KEY, ClassName NVARCHAR(16), Students INT, Teacher NVARCHAR(16));
INSERT INTO TB_Class(ClassName,Students,Teacher) VALUES
('1班',25,'YL'),('2班',18,'WSP'),('3班',24,'YL'),('4班',25,'ZHY'),
('5班',22,'WSP'),('6班',25,'YL'),('7班',20,'ZHY'),('8班',22,'WSP');

五大基礎聚合函數

函數示例結果說明
SUMSELECT SUM(Age) FROM TB_Students WHERE Age>153818+20
COUNTSELECT COUNT(*) FROM TB_Students WHERE Age>152行數,* 或任意非空列同義
AVGSELECT AVG(Age) FROM TB_Students WHERE Age>1519忽略 NULL
MAXSELECT MAX(Age) FROM TB_Students WHERE Age>1520
MINSELECT MIN(Age) FROM TB_Students WHERE Age>1518

GROUP BY 分組聚合

SELECT Teacher, SUM(Students) AS TotalStu, COUNT(*) AS ClassCnt, AVG(Students*1.0) AS AvgStu, MAX(Students) AS MaxStu, MIN(Students) AS MinStu
FROM TB_Class
GROUP BY Teacher
ORDER BY Teacher;

結果:YL 74/3/24.67/25/24 | WSP 62/3/20.67/25/18 | ZHY 45/2/22.5/25/20。

DISTINCT 去重聚合

SELECT COUNT(DISTINCT Teacher) FROM TB_Class;  -- 結果 3 (YL,WSP,ZHY)

先去重再聚合,適用於「有多少位不同老師帶課」等業務。

進階:條件聚合(單掃描多指標)

SELECT
    SUM(CASE WHEN Age>18 THEN 1 ELSE 0 END) AS AdultCnt,
    SUM(CASE WHEN Age>18 THEN Age ELSE 0 END) AS AdultAgeSum
FROM TB_Students;

避免多次掃描表,大表性能提升顯著。

copyright © 萬盛學電腦網 all rights reserved