--获取多列中的最大/最小值,对一行中的数据进行聚合运算的方法
DECLARE @data TABLE
(
Student NVARCHAR(20) NOT NULL,
Chinese INT NOT NULL,
Math INT NOT NULL,
English INT NOT NULL
);
INSERT @data
(
Student, Chinese, Math, English
)
VALUES
(N'张三', 80, 95, 100),
(N'李四', 90, 89, 78),
(N'王五', 92, 96, 88);
--获取最大值,单值
SELECT *, (SELECT MAX(score)FROM (VALUES (list.Chinese), (list.Math), (list.English)) S (score) ) MaxScore
FROM @data list;
--同时获取最大、最小值,有多个值,使用APPLY生成多列
SELECT *
FROM @data list
OUTER APPLY ( SELECT MAX(score) MaxScore, MIN(score) MinScore
FROM (VALUES (list.Chinese), (list.Math), (list.English)) S (score) ) Agg;