22,210
社区成员
发帖
与我相关
我的任务
分享
DECLARE @T AS TABLE (tid INT,A INT,B INT,C INT,D INT,E INT ,Total INT)
INSERT INTO @T
( tid, A, B, C, D, E, Total )
VALUES ( 1001 , 95,96,100,97,92,98),
( 1002 , 92,96,100,97,92,94),
( 1003 , 100,96,100,100,92,99),
( 1004 , 95,96,89,96,92,95)
SELECT * FROM (
SELECT *
FROM @T unpivot([Point] FOR Subject IN ( [A],[B],C,D,E)) a) a
WHERE Point < a.Total
select 'A', A from t where A < 总分
union all
select 'B', B from t where B < 总分
union all
select 'C', C from t where C < 总分
union all
select 'D', D from t where D < 总分