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');
| 函數 | 示例 | 結果 | 說明 |
|---|---|---|---|
| SUM | SELECT SUM(Age) FROM TB_Students WHERE Age>15 | 38 | 18+20 |
| COUNT | SELECT COUNT(*) FROM TB_Students WHERE Age>15 | 2 | 行數,* 或任意非空列同義 |
| AVG | SELECT AVG(Age) FROM TB_Students WHERE Age>15 | 19 | 忽略 NULL |
| MAX | SELECT MAX(Age) FROM TB_Students WHERE Age>15 | 20 | |
| MIN | SELECT MIN(Age) FROM TB_Students WHERE Age>15 | 18 |
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。
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;
避免多次掃描表,大表性能提升顯著。